Separating weekdays from weekends

Michael Ott

Hi everyone,

 

this is probably something very simple, but I still struggle, hope you can help. 

 

I have a large timesheet with one raw per day, every day of the year. 

 

The contractors will get paid higher rates on weekends, compared to work days. Initially I thought of putting an extra checkbox "weekend" column next to the date, but this is a manual solution. 

 

Is there a way of building a formula that automatically checks if the date column is a workday or a weekend and applies different rates to the dollar calculations?

 

Thank you!

Dietrich Koch

Hi Michael,

 

 

Dates are displayed in german time format. The work in the first row was done on Friday, in the second on Saturday.

 

The formula to check wich rate to take is in the cells of column "Total":

 

=IF(OR(WEEKDAY(Date1) = 1, WEEKDAY(Date1) = 7), Hours1 * $Activity$6, Hours1 * $Activity$5)

 

Michael Ott

Danke schön, Dietrich, appreciate your help!

 

Best regards

Michael

Kennedy Stomps

Great solution, Dietrich! Thanks for sharing

Jerry Tutt

Thank you Dietrich!  This is going to be very useful to me in many ways in scheduling legal activities.

Jerry Tutt

One question...what is the significance of the $ signs in the formua?  

 

Thanks,

 

jerry

Dietrich Koch

Jerry,

 

the $ is used in formulas to fix references!

 

In the example above the value "$Activity$6" says Smartsheet to take the value of Column "Activity" in the row "6". One can copy the formula and it will always reference to this cell. E.g. the formula would be "$Activity6" instead and you copy the formula down to the next row it would be automatically adapted to "$Activity7" and so one. This also works with columns.

 

There is an help article that describes the various use cases for this fixed and relative references in formulas: http://help.smartsheet.com/articles/775363-using-formulas

 

Scroll down to the paragraph:

Using Drag-Fill to Save Time