Formulas for Calculating Time
Comments
-
Hello, I made a timesheet and wage calculation table.
The data is sent by employees using a form.
I have a problem with the time calculation.🙄
Here is the sheet.
Below is what I don't understand and I don't know where the error is.
I would be grateful if someone could check it. Thanks!
-
@Katie Lafferty That is all very helpful. I will try to work on this as I can. Hopefully by the end of next week.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
-
Sorry, but I messed up the example in my previous post. It shows the end time incorrectly. Below is the corrected one.
-
@Csuzi The solution you are using does not take into account shifts. The solution you are using is for total duration.
8:00 on 3 October until 16:00 on 4 October is 32 hours.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, you are right. It should work for me if the user worked from 8:00 a.m. to 4:00 p.m. on October 3rd and 4th.
-
This Post is literallly a lifesaver. I used Conversion of timezones (Solved Formula Included), the only change i made was that i noticed if the created date was exactly on the hour it only added a single 0 for minutes column (which no big deal but i'm crazy) but i just edited the formula for minutes do say that if it equal 0 then make it 00 instead . =IF(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2)) = 0, "00", VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2)))
-
@Bob.Cozzy I'm glad you were able to make it work for you!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I am sorry if this has already been answered but this thread is now over 12 pages long and I can't find the solution I am needing. It's probably very simple. I just need a time formula to convert 13:04 to 1:04 PM or 18:13 to 6:13 PM or 7:57 to 7:57 AM etc. Basically, converting military time to standard time.
Thank you!
-
@McAfee Electric Mgr I don't believe it has been answered yet, but give this a try...
=(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) - IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1))> 12, 12, 0)) + ":" + MID([Time Column]@row, FIND(":", [Time Column]@row) + 1, 2) + " " + IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) >= 12, "PM", "AM")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks @Paul Newcome ! That worked perfectly for what I needed. :-)
-
@McAfee Electric Mgr Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This can be simplified a bit.
=SUBSTITUTE(MOD(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)), 12), 0, 12) + ":" + RIGHT([Time Column]@row, 2) + IF(IFERROR(VALUE(LEFT([Time Column]@row, 2)), 0) > 11, " PM", " AM")
-
@L_123 I like it. I hadn't thought about the SUBSTITUTE/MOD, but the reason I went with MID instead of RIGHT was because I wasn't sure what else may or may not be in the cell. Just playing it safe on that one.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome I realized you did that, I actually started with that in mind as well, but changed it at the last second after re-reading his message. I thought I could make it shorter than I did is why I started on it, ended up not being much shorter than yours. I honestly don't know which one is more/less resource intensive, mine may be shorter, but it also performs more iterative calculations.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives