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.
-
-
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. :-)
-
-
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.
