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 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.
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives