Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Separating weekdays from weekends

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
edited 07/21/16
Options

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)

• Options

Danke schön, Dietrich, appreciate your help!

Best regards

Michael

• Employee
Options

Great solution, Dietrich! Thanks for sharing

• ✭✭✭
Options

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

• ✭✭✭
Options

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

Thanks,

jerry

• ✭✭✭✭✭✭
Options

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

This discussion has been closed.