Simple dates subtraction in Excel

  jonmac 19:25 07 Apr 2009

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 2009

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 2009

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 2009

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

  jonmac 20:38 07 Apr 2009

Yes, dd/mm/yy

  VoG II 20:46 07 Apr 2009

Re-type the problem dates?

  jonmac 20:56 07 Apr 2009

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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?