Tech Support Guy banner
Status
Not open for further replies.
1 - 3 of 3 Posts

·
Registered
Joined
·
1 Posts
Discussion Starter · #1 ·
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)))
 

·
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
 

·
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.
Top