Calculatnig the difference between two dates and times

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Moll45

Good afternoon knowledgeable people,

Can someone please help and tell me how I can calculate the difference between two columns both formatted as dd/mm/yyy hh:mm.

I do need to be specific to hours and minutes - is this possible.

Many thanks as ever,

Moll

XL Guru

>> I do need to be specific to hours and minutes ...

... and to which program you're using. Perhaps it's Excel ; if so, and assuming the data is in columns A & B, just use a formula in column C such as

=B1-A1

The only hitch is you will need to custom format the result cells. Hit CTRL+1, on the Number tab select the Custom category, create a type such as

[h]:mm

Once you've done this for one cell, use Format Painter to apply to the whole range or column.

Rgds,
Andy

Moll45

Hi Andy,

Yes sorry I should've said it is Excel .

e.g.

20/04/2004 11:00 - 21/04/2004 08:00 = 21:00
20/04/2004 11:00 - 21/04/2004 10:30 = 23:30
20/04/2004 11:00 - 21/04/2004 09:05 = 22:05
20/04/2004 11:00 - 21/04/2004 11:15 = 00:15
20/04/2004 11:00 - 21/04/2004 18:00 = 07:00

As you can see if the second time is earlier than the first it works out ok, but if the first time is lower than the second I'm 24 hours out, unfortunately I don't know enough about Excel to be able to automatically fix this.

Thanks,

Moll

XL Guru

Moll,

works for me. Must be something to do with the format(s). Check your setting is as the picture. If no joy, zip & post your file.

Rgds,
Andy

Attachments

• 68.9 KB Views: 53

XL Guru

I get the same results as you posted if I don't have the "h" in [] brackets. These are used to force (display of) time calcs > 24 hours.

Rgds,
Andy

Moll45

Success - thank you so much -

Moll

XL Guru

Glad to help. Have a good weekend.

Rgds,
Andy

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On