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

JE McGimpsey
Guest
Posts: n/a

 10th Mar 2004
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

In article <F2028CC3-348B-4182-95EE-(E-Mail Removed)>,
Sov <(E-Mail Removed)> wrote:

> I have two columns of dates (dd/mm/yyyy) - I need to create a formula to
> calculate the number of days between the dates. Excel can do it by placing
> each dates within "" - but I need a general formula to do thousands of
> entries.
> Do I need to have particular cell format before entering any equation?
>
> Help! I'm rather frustrated.

=?Utf-8?B?U292?=
Guest
Posts: n/a

 10th Mar 2004
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

C

Norman Harker
Guest
Posts: n/a

 10th Mar 2004
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 Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sov" <(E-Mail Removed)> wrote in message
news:CC884BC0-E456-4A82-8D33-(E-Mail Removed)...
> I'm afraid that neither of the methods listed above are proving to

>
> 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?
>
>
> CJ
>

=?Utf-8?B?U292?=
Guest
Posts: n/a

 11th Mar 2004
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
Guest
Posts: n/a

 11th Mar 2004
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 Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sov" <(E-Mail Removed)> wrote in message
news:A50258C6-F372-4673-B899-(E-Mail Removed)...
> 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

=?Utf-8?B?U292?=
Guest
Posts: n/a

 11th Mar 2004
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

Chri

Norman Harker
Guest
Posts: n/a

 11th Mar 2004
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 Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Sov" <(E-Mail Removed)> wrote in message
news:E64D3548-8415-46A9-B313-(E-Mail Removed)...
> 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.
>

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

Norman Harker
Guest
Posts: n/a

 11th Mar 2004
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 Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Norman Harker" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Bob Phillips Microsoft Excel Worksheet Functions 1 11th Jan 2007 05:44 PM Roger Govier Microsoft Excel Worksheet Functions 0 11th Jan 2007 01:13 PM richard.goodger@gmail.com Microsoft Excel Misc 5 26th Oct 2005 06:18 PM themax16 Microsoft Excel Worksheet Functions 2 21st Oct 2005 01:38 PM Frank Kabel Microsoft Excel Misc 0 10th Mar 2004 07:06 PM

Features