Creating timesheet with formulas

Majid Kayani

Hi All,

I am a newbie to Smartsheet's and this Community. I require some urgent help in regards creating a timesheet for project.

I am getting a lot of error when trying to create this sheet in Smartsheet's. I Originally created this in excel with formulas however when i have imported this file over into Smartsheet's all my formulas are unparseable.

so I have now decided to start fresh in Smartsheet's.

The issue I am having is from a title of "start recording" I have a drop down of "YES" when I click yes from the drop down I would like it to give me the date and time in separate columns. furthermore when I press stop recording it will provide me the updated date and time.

is this achievable? as I really require some help on this. I am racking my brain.

Thanks in advance

Garrett Henke

You may need to adjust how you record the time in the your smartsheet. However this is the formula you will need to use to find the difference between and start and end time:

=INT((((VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1)) + IF(FIND("p", [Finish Time]1) > 0, IF(VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1)) < 12, 12), IF(VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1)) = 12, -12))) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1)) + IF(FIND("p", [Start Time]1) > 0, IF(VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1)) < 12, 12), IF(VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1)) = 12, -12))) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) / 60) + ":" + IF(((VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2))) + IF(VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)) < 0, 60)) < 10, "0" + ((VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2))) + IF(VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)) < 0, 60)), ((VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2))) + IF(VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2)) - VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)) < 0, 60)))

Majid Kayani

Hi

thank you for the reply.

i was wondering I would edit a column so that its give me the time.