# Formulas for Calculating Time

• ✭✭

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.

• ✭✭
edited 10/06/22
• ✭✭

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.

• ✭✭

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!

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

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

• ✭✭✭✭✭✭
• ✭✭✭✭✭✭
edited 10/26/22

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.

• ✭✭✭✭✭✭

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