# Excell Date Math

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.

#### hillrp

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

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

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

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