Date formual issue

  • Thread starter Thread starter Martyn
  • Start date Start date
M

Martyn

Hi everyone

I have two colums with dates in and I want to subtract one from the other so
so I can get hte dumber of days between the two, this is the east part. The
problem I have is when one of the colums is blank I want to use todays date
so I can avoid #VALUE! being returned, I been reading a looking up posts here
for the best part of two hours and can not find the answer, can anyone help
me please?

Many thanks
Martyn
 
Hi everyone

I have two colums with dates in and I want to subtract one from the other so
so I can get hte dumber of days between the two, this is the east part. The
problem I have is when one of the colums is blank I want to use todays date
so I can avoid #VALUE! being returned, I been reading a looking up posts here
for the best part of two hours and can not find the answer, can anyone help
me please?

Many thanks
Martyn
Click to expand...


Try this:

Replace B1 with IF(ISBLANK(B1),TODAY(),B1)

To get the number of days between two dates you just have to subtract
them.

=IF(ISBLANK(B1),TODAY(),B1)-IF(ISBLANK(A1),TODAY(),A1)

Hope this helps / Lars-Åke
 
If you really want to use today's date then use:

=A1-(MAX(TODAY(),B1))

or:

=MAX(A1,TODAY())-B1

depending of which date will be blank or:

=IF(COUNT(A16:B16)<>2,"",A16-B16)

to only calculte when there are two dates showing.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top