Simple dates subtraction in Excel

  jonmac 19:25 07 Apr 09

I have a table with start dates in col 1, end date in col. 2 and difference in days in col 3.
However for some reason the formula, say, =B1-A1 doesn't always work. In some lines it's OK and in others it displays #VALUE! The formatting of the various cells appears to be OK yet the symptoms persist.
I'm stumped. Any solutions?

  VoG II 19:51 07 Apr 09

You will get that error if one (or both) of the dates is actually a text string, not a date. To test this, assuming that a problem date is in A1 then in a spare cell try the formula


If that returns FALSE it is a 'text date'.

Id that is the case then, with one column at a time, select the dates, Data > Text to Columns, click Next twice then on the third screen tick Date and select DMY then click Finish.

  jonmac 20:21 07 Apr 09

Thanks Vog, there are indeed true and false values returned by one of the columns. How that happened I don't know.
Unfortunately your ssuggested solution doesn't have any effect on single or multiple cells. Could you review your solution, please?

  VoG II 20:28 07 Apr 09

Are the dates actually in the format dd/mm/yy or similar or in another format?

  jonmac 20:38 07 Apr 09

Yes, dd/mm/yy

  VoG II 20:46 07 Apr 09

Re-type the problem dates?

  jonmac 20:56 07 Apr 09

Oops! Just realised what the problem is. I have typed in 30/02 each year instead of 28 or 29!!! There's always a solution isn't there. Never mind, I've learnt something new from you. Thanks again for your time and assistance VoG.

This thread is now locked and can not be replied to.

Qualcomm Snapdragon 835 benchmarks: Antutu, Geekbench 4, GFXBench and PCMark results

1995-2015: How technology has changed the world in 20 years

This animated short film tells a moving story of domestic violence for Refuge

New iPad 2017 preview: Apple's affordable but underspecced new iPad may appeal to the education…