Excel Date Formula

[robcarr]

Dear All,

I am using this formula
=IF(B11=0," ",IF(D11=0,($N$1-B11)," &quot) to calculate the difference between 2 dates based on the current date in N1, the formula is not working correctly, as it disregards the month, it always calculates the no. of days between say 1 and 20, but when the length of time goes over a month it does not take this into account.

Example.

b11 = 15th may,
n1 = todays date
result = 12 days
should be a lot more. Does anybody know why this is happening.

Thanks in advance,

 

[xlbo]

Works fine for me - 3 questions:
How are you entering "15th May".... as 15/5/02 or as 15th May or what ?
What is the formatting for the cell with the formula?
How are you entering today's date?
Rgds
Geoff

 

[robcarr]

I enter the date in B11 as 15/05 - the cell is formatted to show the date as 15-May, the date in N1 is the formula =now() formatted as 27/06/02 10:21, the format for the result in E11 is 20 days - custom d "days".

Hope this helps.

 

[Loomah]

Ah, Rob, dates
Have you seen or even heard of the 'DATEDIF' function? I hadn't until I saw it on another posting. Anyway, the syntax is =DATEDIF(start_date,end_date,unit)

For unit use "D" in your case. It is in the help files but doesn't seem to be listed in my function wizard. Bizaar.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[xlbo]

Right - I know what the problem is then - If you want to use your current formula, you need the format of [d] rather than d. If you format as d, you will get a maximum of 31. The [] allow the number to go higher than the intrinsic maximum of the format - 31 for days, 12 for months, 60 for seconds etc etc. (this applies to all date and time formats). What the cell is telling you is that there is 1 month and 20 days but because you have formatted as d only, you only get the d portion of that. change the format to [d] and it'll work fine. Otherwise, Loomah's suggestion should work fine also, tho you'll have to change the format of the cell from "d" to number or general
HTH
Geoff

 

[cheerio]

From memory, DATEDIFF is not supported by all versions of Office or by all applications within Office as a front end function (eg on a spreadsheet or in an Access query). For example Access 2000 shows it in help; Excel 2000 does not show it in the function wizard.

It tends to be more widely available in VBA so if you want portability it can help to embed the VBA version in a custom function.

It is a slightly dangerous function because results are not necessarily what you would expect. For example, if you set the interval to years it thinks that the gap from 31 Dec 2002 to 1 Jan 2003 is a year. This means you can come to grief if you are trying to work out peoples' ages.

Ken

 

[macbone2]

date 1 in cell A1, date 2 in cell B1, then in c1, '=DAYS360(A1,A2)' THIS WILL RETURN AN EXACT NO. OF DAYS. IF B1 IS EARLIER THA A1, YOU GET A NEGATIVE NUMBER. IF EITHER DATE IS INVALID (E.G. 31/6/2002 YOU GET #VALUE)

IT'S A STANDARD FUNCTION

CHEERS

GEOFF