Time Sheet Formulas

Angie M Graham

With the help of this community and @Paul Newcome and @Andrée Starå I have created a weekly time sheet for my employees, that can display the day of the week given a date and calculate the hours worked in a day given in, out and lunch break times. The formulas work great! However, I have an automation set up that moves rows at the end of the week making the time sheet a new sheet each week. I do this because I have dashboards set up so that just the current weeks' time is displayed for the employee to know how many hours they have worked for the week. My issue is, the formulas that work great, do not save to the cleared out sheet. I have tried embedding them as a hidden field in forms and I have tried creating a workflow using new cell value and both put an apostrophe (') at the beginning of the formula making it not function correctly.

I have 30 employees and to go in each week and re-enter these formulas would be too time consuming. Is there another way to accomplish this?

Thanks for the help!

Paul Newcome

Have you tried applying them as Column Formulas?

Angie M Graham

I have tried going to the column properties and making it a single select drop down but one of the formulas has too many characters. Not sure if that is what you meant by "column formula."

Paul Newcome

Right click on a CELL that has the formula already in it. Then you should be able to click on the very bottom selection to turn "Convert to Column Formula".

Angie M Graham

Here is one formula:

=IF(WEEKDAY(Date1) = 1, "Sun", IF(WEEKDAY(Date1) = 2, "Mon", IF(WEEKDAY(Date1) = 3, "Tue", IF(WEEKDAY(Date1) = 4, "Wed", IF(WEEKDAY(Date1) = 5, "Thu", IF(WEEKDAY(Date1) = 6, "Fri", IF(WEEKDAY(Date1) = 7, "Sat")))))))

When I try to convert to column formula, I get this error...

"The column formula isn't quite right, see our help article."

I tried changing (Date1) to (Date) and I get an #Unparseable

I tired changing (Date1) to (Date:Date) and I get #Invalid Data Type

The other formulas I was able to convert to a column formula.

Andrée Starå

Hi @Angie M Graham

I hope you're well and safe!

Try something like this. (you can't reference a specific row number. if you want to use it as a Column Formula)

=
IF(WEEKDAY(Date@row) = 1, "Sun", 
IF(WEEKDAY(Date@row) = 2, "Mon", 
IF(WEEKDAY(Date@row) = 3, "Tue", 
IF(WEEKDAY(Date@row) = 4, "Wed", 
IF(WEEKDAY(Date@row) = 5, "Thu", 
IF(WEEKDAY(Date@row) = 6, "Fri", 
IF(WEEKDAY(Date@row) = 7, "Sat")))))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Angie M Graham As Andree said, You will want to use "@row" instead of an actual row number.