# Excell Date Math

Discussion in 'Business Applications' started by hillrp, Sep 5, 2004.

Not open for further replies.

Joined:
Sep 5, 2004
Messages:
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)))

2. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331
Try,

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

3. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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

As Seen On