Results 1 to 8 of 8
Thread: Time Calculations (W2K)

20040211, 14:15 #1
 Join Date
 Mar 2003
 Location
 Canton, Ohio, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Time Calculations (W2K)
II have a spreadsheet that user enters the time a person works at a position. It has start time, stop time and their initials. This information is taken off of a log that is required to be filled out using UTC. Currently the difference between UTC time and local time is 5 hours. If a person stars working the position at 18:30 local time, they write on the log 23:30. If that same person stops working that position at 19:30, they write down 00:30 and so on.
In order to subtract those two times, I convert the UTC time to local time by using this formula:
=IF((A4>$B$1),A4$B$1,ABS(A4+$A$1))
A1= 19:00
A4 = the start time
B1= 5:00
The same goes for the stop time. All of this works great except when the start time is midnight local which is 05:00 UTC. When the user enters 05:00, the formula converts it to 24:00. If I try to subtract 24:00 from 02:00, it won

20040211, 14:38 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Time Calculations (W2K)
Excel does convert 24:00 to 0:00, it just happens to be on the next day. If you include the days, you can manipulate directly. If you don't include the dates, you will have to do something to remove the "day portion" does: does this work?
<pre>=mod(IF((A4>$B$1),A4$B$1,ABS(A4+$A$1)),1)</pre>
The time is only the decimal part of the day.
If you want them to enter a number and not enter in the ":" and have it "convert" you can use a formula like:
<pre>=TIMEVALUE(REPLACE(TEXT(A1,"0000"),3,0,":") )</pre>
Steve

20040211, 15:19 #3
 Join Date
 Mar 2003
 Location
 Canton, Ohio, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Time Calculations (W2K)
Thanks Steve, That all worked.

20040211, 15:32 #4
 Join Date
 Mar 2003
 Location
 Canton, Ohio, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Time Calculations (W2K)
I quess I spoke a little too soon. The conversion part works, but I must be putting your answer to my second question in the wrong place. Where would I put your formula in the spreadsheet?
=TIMEVALUE(REPLACE(TEXT(A1,"0000"),3,0,":"))

20040211, 15:44 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Time Calculations (W2K)
You would use this to convert any of the "entries". If they enter in A4 you could put the calc in some cell (eg E4):
=TIMEVALUE(REPLACE(TEXT(A4,"0000"),3,0,":"))
Then in your formula you would refer to E4 rather than A4:
=mod(IF((E4>$B$1),E4$B$1,ABS(E4+$A$1)),1)
If you did not want the intermediate formula you would have to make the "megaformula"
=mod(IF((TIMEVALUE(REPLACE(TEXT(A4,"0000"),3,0,":" ))>$B$1),TIMEVALUE(REPLACE(TEXT(A4,"0000"),3,0,":" ))$B$1,ABS(TIMEVALUE(REPLACE(TEXT(A4,"0000"),3,0,":" ))+$A$1)),1)
to convert in only 1 formula.
Steve

20040211, 16:03 #6
 Join Date
 Mar 2003
 Location
 Canton, Ohio, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Time Calculations (W2K)
Thanks again Steve. You have really helped, but if I could impose on last time. What you suggested works, but it changes the entered value to 0:00 no mater what the user enters. For instance, if the user enters 1230 as a start time, the time is correctly converted to 7:30, but the value in the start time column is 0:00. It would work, but it would make it hard for the user to go over their work if they were looking for a mistake or typo. You have given me what I need to get this project working; can you also help correct this last item?

20040211, 16:15 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Time Calculations (W2K)
If I understand correctly when the user enter 1230, the "display" shows "0:00". If this is the problem, just change the format to "general".
When you format to date, excel interprets the DECIMAL portion of the number entered. the decimal portion of 1230 (an integer with no decimal portion), there so excel puts a "0:00" as the time. Get rid of the time format and all should be "right with the world"
Steve

20040211, 16:36 #8
 Join Date
 Mar 2003
 Location
 Canton, Ohio, USA
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Time Calculations (W2K)
That did it, Have a great day.