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.
thinkspi.com


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.
thinkspi.com

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([email protected], FIND(":", [email protected]) + 1, 2)) = 0, "00", VALUE(MID([email protected], FIND(":", [email protected]) + 1, 2)))

@Bob.Cozzy I'm glad you were able to make it work for you!
thinkspi.com

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")
thinkspi.com

Thanks @Paul Newcome ! That worked perfectly for what I needed. :)


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.
thinkspi.com

@Paul Newcome I realized you did that, I actually started with that in mind as well, but changed it at the last second after rereading 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.