Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    2 Star Lounger
    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.

  4. #4
    2 Star Lounger
    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,":"))

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 "mega-formula"
    =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

  6. #6
    2 Star Lounger
    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?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    2 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •