Comparable Excel Formula?

  • Thread starter Thread starter Lee Hunter
  • Start date Start date
L

Lee Hunter

The task is to calculate the elapsed time between two
values. The first is a scheduled time with no associated
date (1/0/00 13.20)i.e. the same time every day. The
second is an actual time with it's asociated date
(7/31/04 13:30). The difference is negative 10 minutes
or 10 minutes late

The formula which works in Access is:
Var: DateDiff("n",FormatDateTime(Date1,4),FormatDateTime
(date2,4))

Is there a comparable formula in Excel?
 
Lee Hunter wrote...
The task is to calculate the elapsed time between two values.
The first is a scheduled time with no associated date (1/0/00
13.20) i.e. the same time every day. The second is an actual
time with it's asociated date (7/31/04 13:30). The difference is
negative 10 minutes or 10 minutes late
Click to expand...
...

Maybe something like

=IF(Date1=MOD(Date2,1),"On Time",ABS(Date1-MOD(Date2,1))&
" minutes "&IF(Date1<MOD(Date2,1),"late","early"))

One difficulty in Excel is that Excel doesn't handle negative tim
easily or well. You can't use time number formats with negative tim
values. However, if all you want is the numeric result,

=Date1-MOD(Date2,1)

would work
 
Back
Top