Help with Datedif based on certain conditions...

Leechmil

New Member
Joined
Aug 21, 2007
Messages
6
Sorry for being a total noob but I'm trying to do something and it's totally
throwing me!

Basically I want to take a date contained in cell H4 and compare it to todays date,

then work out the difference between the two in days, this is working ok using the

datedif function...

=DATEDIF(H4,TODAY(),"d")

However if cell H4 is blank or the date contained in H4 is above todays date it displays #NUM! - is there anyway to hide this?

Thanks in advance for any help!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
=if(iserror(DATEDIF(H4,TODAY(),"d")),"Something Wrong",DATEDIF(H4,TODAY(),"d") )
 

Leechmil

New Member
Joined
Aug 21, 2007
Messages
6
Steve, thanks for your super-fast reply, much appreciated! that formula worked perfectly but now I presented with another minor problem!

Basically if the cell H9 mentioned previously contains no date then the output cell automatically displays 39315 days!
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
=if(iserror(DATEDIF(H4,TODAY(),"d")),"Something Wrong",if(h4>0,DATEDIF(H4,TODAY(),"d"),"Date Missing") )
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Sorry for being a total noob but I'm trying to do something and it's totally
throwing me!

Basically I want to take a date contained in cell H4 and compare it to todays date,

then work out the difference between the two in days, this is working ok using the

datedif function...

=DATEDIF(H4,TODAY(),"d")

However if cell H4 is blank or the date contained in H4 is above todays date it displays #NUM! - is there anyway to hide this?

Thanks in advance for any help!

=IF(H4,DATEDIF(H4,TODAY(),"d"),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,384
Messages
5,836,940
Members
430,463
Latest member
mikmob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top