I am having my team track their time. I have a column for a start time and end time. Right now I am using military time )0 to 24 but I would like to know if there is a way to use normal time so my team can use 1pm to 3pm.
Yes, it is!
I'll get back to you when I find the post.
Look here: https://community.smartsheet.com/discussion/calculate-time-between-two-dates-days-hours-and-minutes
Hope that helps!
Have a fantastic week!
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
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.
Is there a possibility of overlapping midnight such as 8pm - 4am? Will you need to track multiple days such as 1/1/19 (time) - 2/2/19 (time)?
The single task of converting 12 hour to 24 hour is actually pretty straight forward.
You would pull the hours portion of the time using this:
=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1))
and use a basic IF statement to add 12 to the hours if it find a "p" in the time something along the lines of:
=IF(FIND("p", LOWER([Time Column]@row)) > 0, 12, 0) + VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1))
Now that you have converted the hours to a 24 hour clock, you can use that times 60 to convert it into minutes and add the minutes from the [Time Column].
Do this to both the Start Time and Finish Time. Once you subtract Start from Finish, you'll have the total minutes that the task took.
You can then use something along the lines of this to determine how many hours that was:
=[Difference in Minutes]@row / 60
This will give you the result of (for example) 6.5 for six and a half hours. If you would rather six and a half hours displayed as 6:30, it will take just a couple of different/extra steps.
=INT([Difference in Minutes]@row / 60)
gives you the hours.
=[Difference in Minutes]@ row - (INT([Difference in Minutes]@row / 60) * 60)
will give you the minutes.
=INT([Difference in Minutes]@row / 60) + ":" + [Difference in Minutes]@ row - (INT([Difference in Minutes]@row / 60) * 60)
would give you the result of hh:mm
I've been trying to do a simple check of whether or not the entry from the form has a returned value that is either yes or no and it checks 3 cell locations to see if any returned yes. The 3 columns are Dropdown lists for Yes/No and the Status is a Text/Number, is that the issues? If not how do i fix that?
I'm wondering if there is a way to lock rows 1-12 out of the sorting and filtering of a sheet. I might have take out that part of the from otherwise I dont like how the built in dashboard moves when a sort or filter is applied?
Hello, I apologize if this has already been answered, I did search before asking. I recently downloaded a template and it had the google slides displayed in presentation mode. I cannot figure out how to do this as when I embed, it's just the entire google page where you can edit and see the menu bar. How do I embed the…
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.