 Status
Not open for further replies.
1 - 3 of 3 Posts

#### hillrp

·
##### Registered
Joined
·
1 Posts
Discussion Starter · ·
Is there a way to enter a simple formula or routine in Excell to subtract one date/time from another and get a result in hours:minutes? I have tried several formulas, and got one that works 99% of the time, except when the same number of minutes is in both dates. When that occurrs, the hour is one less. My formula is:

=CONCATENATE(INT((B1-A1)*1440/60),":",IF(MINUTE(B1)-MINUTE(A1)<0,MINUTE(B1)-MINUTE(A1)+60,MINUTE(B1)-MINUTE(A1)))

#### maxflia10

·
##### Registered
Joined
·
331 Posts
hillrp said:
Is there a way to enter a simple formula or routine in Excell to subtract one date/time from another and get a result in hours:minutes? I have tried several formulas, and got one that works 99% of the time, except when the same number of minutes is in both dates. When that occurrs, the hour is one less. My formula is:

=CONCATENATE(INT((B1-A1)*1440/60),":",IF(MINUTE(B1)-MINUTE(A1)<0,MINUTE(B1)-MINUTE(A1)+60,MINUTE(B1)-MINUTE(A1)))
Try,

B1-A1 and custom format the cell that houses the formula as [h]:mm

#### Zack Barresse

·
##### Registered
Joined
·
5,458 Posts
The only thing you'll need to watch for (using maxflia's solution - which is brilliantly simple ) is having A1 greater than B1. If this is possible you may want to look into a possible IF function integration ...

=IF(A1>B1,"Out of Range",B1-A1)

HTH

1 - 3 of 3 Posts
Status
Not open for further replies.