PDA

View Full Version : Solved: Formula take 2



austenr
08-25-2010, 07:01 AM
Using this formula:

=DATEDIF(spdob,dor,"m")/12

where spdob = spouse date of birth
dor = date of retirement

if i dont enter a spdob the cell the formula is in returns 111. Is this a default? I need it to say 0 if no spdob is entered.

How can I correct this? Thanks

Bob Phillips
08-25-2010, 07:09 AM
It takes the empty date as 1/1/1900, so the number of months will be however many from there until the dor date, which is 1327 to now.

austenr
08-25-2010, 07:12 AM
ok, can i force it (the cell) to be zero if the first argumentof the datedif is blank?

Bob Phillips
08-25-2010, 07:18 AM
It already assumes that.

austenr
08-25-2010, 07:21 AM
I meant the formula the cell is in. Right now it shows 111, i want it to be zero if spdob is blank. Sorry.

Bob Phillips
08-25-2010, 07:33 AM
Sorry, I see what you mean.

=IF(spdob="",0,DATEDIF(spdob,dor,"m")/12)

austenr
08-25-2010, 07:35 AM
thanks Bob.