Creating timesheet with formulas
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
Answers
-
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)))
-
Hi
thank you for the reply.
i was wondering I would edit a column so that its give me the time.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!