Formulas for Calculating Time

Csuzi

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!

Paul Newcome

@Katie LaffertyThat is all very helpful. I will try to work on this as I can. Hopefully by the end of next week.

Csuzi

Csuzi

Sorry, but I messed up the example in my previous post. It shows the end time incorrectly. Below is the corrected one.

Paul Newcome

@CsuziThe 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.

Csuzi

@Paul Newcome

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.

Bob.Cozzy

This Post is literallly a lifesaver. I usedConversion of timezones (Solved Formula Included),唯一的变化是,我注意到如果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)))

Paul Newcome

@Bob.CozzyI'm glad you were able to make it work for you!

McAfee Electric Mgr

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!

Paul Newcome

@McAfee Electric MgrI 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")

McAfee Electric Mgr

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

Paul Newcome

@McAfee Electric MgrHappy to help.️

L_123

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")

Paul Newcome

@L_123I 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.

L_123

@Paul NewcomeI 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.