Re: how to calculate the number of days between dates (dd/mm/yyyy) using a formula

[JE McGimpsey]

XL stores dates as integer offsets from a base date. So to calculate the
difference, just subtract. XL for some reason formats the cell as a
Date, so you'll have to reformat it as General or another number format:

A1: 03/10/2003
A2: 03/10/2004
A3: =A2-A1 ===> 366

 

[Guest]

I'm afraid that neither of the methods listed above are proving to be helpful

Just subtracting one cell from another does not seem to work
What am I doing wrong
Dates are listed as dd/mm/yyyy - and have tried formatting them using (dd/mm/yyyy) under custom settings. Also tried general format. No difference. Always getting #VALUE
I tried the formula =DATEDIF(Date1,Date2,Interval) as =DATEDIF(A1,A2,d)
but still #VALUE
Is there something wrong with any of my settings or cells formatting

Thanks in advanc

C

 

[Norman Harker]

Hi Sov!

It looks like your dates are not dates but are text.

Check using =ISTEXT(A1)

If this returns TRUE, then you have text.

Convert to numbers by
Select a blank cell
Copy
Select the "dates"
Edit > Paste special > Add
OK

Now you should see the date serial number
Format as dd/mm/yyyy

Use B1-A1 to calculate the days difference.

#VALUE! is the error message that indicates the wrong type of argument
or operand is used and in this case it's because you are trying to use
maths on text.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I'm afraid that neither of the methods listed above are proving to be helpful.

Just subtracting one cell from another does not seem to work.
What am I doing wrong?
Dates are listed as dd/mm/yyyy - and have tried formatting them

using (dd/mm/yyyy) under custom settings. Also tried general format.
No difference. Always getting #VALUE!

 

[Guest]

Dear All - thanks for help so far!

But still not working
Tried Norman's suggestion. Indeed they were listed as text. So followed his suggestions
Most of dates remained the same but became left justified and even then changing their format to the date code of dd/mm/yyyy had no affect
A few of the dates turned into serial numbers, which become proper dates once the cell format code has been changed
Would the whole lot be affected by some boxes having text rather than a date - although there are only a few of these out of over 3000 entries

Yours still very much confused - but grateful for any help

Chris

 

[Norman Harker]

Hi Sov!

OK! The problem you now have is one of a conflict with your Regional
Date settings. That is why some of the dates are being converted to
dates and others left as text. The trouble is that even the converted
dates are wrong because the day number is being treated as the month
number and the month number is being treated as the day number.
Conversion fails where the day number is greater than 12.

There are ways involving the changing the Regional settings but I
think that I would prefer to parse the original text data.

Set up a helper column and format for dates as dd-mmm-yyyy [I prefer
that because it is unequivocal. You can change it later]

Now use the formula:

=DATE(--RIGHT(A1,4),--MID(A1,4,2),--LEFT(A1,2))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Dear All - thanks for help so far!!

But still not working.
Tried Norman's suggestion. Indeed they were listed as text. So followed his suggestions.
Most of dates remained the same but became left justified and even

then changing their format to the date code of dd/mm/yyyy had no
affect.

A few of the dates turned into serial numbers, which become proper

dates once the cell format code has been changed.

Would the whole lot be affected by some boxes having text rather

than a date - although there are only a few of these out of over 3000
entries.

 

[Guest]

Dear Norma

Thanks for your patience and perserverence

But still no joy
(Quick question - a helper column is simply a spare empty column?
Into this spare column (which I have formatted as dd-mmm-yyyy - as suggested) do I then place the formula
If this is so - tried it and all I got was: #VALU

I also tried formatting the dates into the new format (dd-mmm-yyyy) - had no effect - only those dates which were already right-aligned changed to the new format
Applying the formula to them created strange numbers / dates - dependent on how that cell was formatted

Data is listed as (e.g.)
25/03/194
29/12/1943
04/03/1942
22/09/1942
04/02/1942
20/02/1943
Two seperate columns - difference between the two date columns is likely to range from a few days to several years

Any further help - please prepare your answers in a fool proof way as I am now terrified that I am perhaps applying your suggestions to incorrect columns, formulae etc

Thanks in advance

Chri

 

[Norman Harker]

Hi Sov!

By chance, are there spaces immediately before these dates? I
certainly get that if I copy from your post.

Use two helper columns.

First see if this works to remove spaces:

=SUBSTITUTE(A1," ","")

If not use:

=SUBSTITUTE(A1,CHAR(160),"")

Then use the formula suggested to convert to date.

If desparate, then send a copy of the workbook to me at the address
below.

We never give up!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Dear Norman

Thanks for your patience and perserverence.

But still no joy.
(Quick question - a helper column is simply a spare empty column?)
Into this spare column (which I have formatted as dd-mmm-yyyy - as

suggested) do I then place the formula?

If this is so - tried it and all I got was: #VALUE

I also tried formatting the dates into the new format

(dd-mmm-yyyy) - had no effect - only those dates which were already
right-aligned changed to the new format.

Applying the formula to them created strange numbers / dates -

dependent on how that cell was formatted.

Data is listed as (e.g.):
25/03/1944
29/12/1943
04/03/1942
22/09/1942
04/02/1942
20/02/1943
Two seperate columns - difference between the two date columns is

likely to range from a few days to several years.

Any further help - please prepare your answers in a fool proof way

as I am now terrified that I am perhaps applying your suggestions to
incorrect columns, formulae etc.

 

[Norman Harker]

Hi All!

Confirmed in off-group that it was random spaces in the data that was
causing the problem.

Case closed with another satisfied customer.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.