Time Sheet Formulas

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
    Paul Newcome ✭✭✭✭✭✭

    Have you tried applying them as Column Formulas?

  • Angie M Graham
    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
    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
    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å
    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!


    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!


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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!