Time Difference, 24 hour clock

  • Hi all,


    I would like to get time differences.


    Say I have 4 columns: Start day, start time, end day, end time. I would like to get the difference of the start day and time with the end day and time.


    Problem is, times may be 20:00 on T, and 02:00 on T+1 , giving me a difference of 18 hours. When it should be 4 hours.


    I need some help calculating the true time difference.


    Any help would be greatly appreciated.

  • Re: Time Difference, 24 hour clock.


    Assuming Start Date in A, Start Time in B with the End date and Time in C & D


    =(C1+D1) - (A1+B1)


    And Custom Format the cell using '[h]:mm'

  • Re: Time Difference, 24 hour clock.


    Quote from Grimes0332;767511

    Assuming Start Date in A, Start Time in B with the End date and Time in C & D


    =(C1+D1) - (A1+B1)


    And Custom Format the cell using '[h]:mm'


    Works good for E1 but the fill down isn;t working :S.


    =SUM(C1,D1) - SUM(A1,B1) , works but only if the dates are the same. Doesn't work for the T and T+1's.

  • Re: Time Difference, 24 hour clock.


    Works absolutely fine for me.


    Can't see why, if a formula is correct in one row, why it would be wrong if copied down. The only variable is the data.


    Sample:


    [TABLE="class: thin_grid"]

    [tr]


    [td][/td]


    [TD="align: center"][/TD]
    [TD="align: center"][/TD]
    [TD="width: 75, bgcolor: #ECF0F0, align: center"][/TD]
    [TD="width: 64, bgcolor: #ECF0F0, align: center"][/TD]
    [TD="width: 64, bgcolor: #ECF0F0, align: center"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: #ECF0F0, align: center"][/TD]
    [TD="bgcolor: #FFFFFF"]01/01/2014[/TD]
    [TD="bgcolor: #FFFFFF"]20:00[/TD]
    [TD="bgcolor: #FFFFFF"]02/01/2014[/TD]
    [TD="bgcolor: #FFFFFF"]03:00[/TD]
    [TD="bgcolor: #FFFFFF"]7:00[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: #ECF0F0, align: center"][/TD]
    [TD="bgcolor: #FFFFFF"]02/01/2014[/TD]
    [TD="bgcolor: #FFFFFF"]18:00[/TD]
    [TD="bgcolor: #FFFFFF"]03/01/2014[/TD]
    [TD="bgcolor: #FFFFFF"]06:04[/TD]
    [TD="bgcolor: #FFFFFF"]12:04[/TD]

    [/tr]


    [/TABLE]

  • Re: Time Difference, 24 hour clock.


    Hi Rick Grimes,


    It seems to be the formatting of the cell. The dates need to be in number format. The first row date columns change to a # when I change format to general. Only the first row though.

  • Re: Time Difference, 24 hour clock.


    Quote from wonka1234;767522

    The first row date columns change to a # when I change format to general


    Of course they will - Why would you change the format?


    Please read my first reply carefully and apply that to any cell you work with.

  • Re: Time Difference, 24 hour clock


    Things are not as they seem... Check the actual contents of the Date columns - these are not dates, but strings. You cannot calculate using strings.

  • Re: Time Difference, 24 hour clock


    Quote from Grimes0332;767534

    Things are not as they seem... Check the actual contents of the Date columns - these are not dates, but strings. You cannot calcculate using strings.


    What does that mean? how do I fix this?

  • Re: Time Difference, 24 hour clock


    Dates in Excel are numbers counting sequentially from day 1 (31/12/1899). Today is day 42446. Times are a decimal number - 6:00am is 0.25; 12 Noon is 0.5; 6:00pm is 0.75 etc. Noon today will have a value of 42446.5


    The 'date' you see on a worksheet is simply a formatted representation of the number, that's why you can change the format easily. '17/03/2016' is the same underlying number as 'March 17, 2016'.


    Sometimes, when importing data, Excel is unable to decide if a value is a date or not, In those cases the data is imported as literal text. It might look like a date but it's not a real date to Excel.


    To resolve this you're going to have to convert the data to real dates, example here.

  • Re: Time Difference, 24 hour clock


    It's something you're going to have to understand and is a very common issue with Excel.


    Haven't viewed your latest upload, but if you go back to the copy you uploaded yesterday and change the format of Cells A2 & A3 to 'General' you'll see that A2 changes to a number but A3 stays as is. That's because A2 is a valid date (which is nothing more than a formatted number) but A3 is a string that just looks like a date.


    The link in my other post tells you how to resolve this.


    Is this the same issue as part of this post?

  • Re: Time Difference, 24 hour clock


    Okay ill look more into it. I just keep getting value no matter what I change the cell format too.
    And no that post is different, it pertains to a Pivot table and chart formatting.
    This one is a substraction of times.

  • Re: Time Difference, 24 hour clock


    Quote

    no matter what I change the cell format too



    Formats are nothing more than a definition of how the data is to be displayed. You can format it all you want, it won't make any difference to the calculations. You have to change the underlying data first then you can format it to make it display as required.

  • Re: Time Difference, 24 hour clock


    I tried the solution to post #12. It didn't work.
    I do notice row 56, 42 and 2 manage to work properly. (row 1 is header)

  • Re: Time Difference, 24 hour clock


    Last thing.


    Change the format for Col A back to Date
    Pick a column, any column (Z).
    Add to row 2 in that column


    =DATE(RIGHT(A2, 4), LEFT(A2, 2), MID(A2, 4, 2))


    Copy down, you should skip the few cells that are already valid dates.


    That will give real dates in Col Z.


    Copy the results and PasteSpecial/Values over Col A


    Repeat for the other date column

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!