Excel Date Calculations

Microsoft Excel 2003 (full product)
July 15, 2010 at 06:56:24
Specs: Windows XP
I am trying to calculate the number of days in each specific fiscal year from the new release date up to but not surpassing the original release date.

Original Release Date - 5/26/2010
New Release Date - 2/01/2011

SFY10 - ends 6/30/10
SFY11 - ends 6/30/11

I can find the total number of days from 2/1/11 but cannot figure out how to calculate them between each fiscal year and stop it at the original release date.

See More: Excel Date Calculations

Report •

July 15, 2010 at 08:38:23

I had this:

	A			B		C
2	Original Release Date	26/May/10		
3	New Release Date	01/Feb/11		
4	SFY10 ends		30/Jun/10	35	
5	SFY11 ends		30/Jun/11	216	

The formula in cell C4 is:
The formula in cell C5 is:

The reason the formulas are longer than you might expect is to cope with the situation where either or both of the following occur:
1. The original release was before the start of the prior fiscal year
2. The new release date is after the end of the current fiscal year

Here is an example:

	A			B		C
2	Original Release Date	26/May/10			
3	New Release Date	01/Sep/11			
4	SFY10 ends		30/Jun/10	35	
5	SFY11 ends		30/Jun/11	365

EDate is a function to get the 'same' date a certain number of months before or after the given date.


Report •

July 15, 2010 at 09:24:52
Can't get it to work - it keeps giving me a #NAME error.

Here is some actual data.

M4 - 4/10/2011
N4 - 6/1/2010
O4 - 313 (number of days between M4 and N4)

R1 - 6/30/2010
R2 - 365 (days in fiscal year)
S1 - 6/30/2011
S2 - 366 (days in fiscal year)

In R4 I used formula: =IF($M4<$R$1,$O4,DATEDIF($N4,$R$1,"d")) and it works except when the N1 is 7/1 - because the datedif needs to have the larger number first in the formula or you get an error).

In S4 I used formula: = =IF($M4>$S$1,$S$2,SUM($O4-$R4)) and so on for T4, U4, etc... and just adding to the subtraction in the last Sum formula.

I am getting errors when N=7/1/2010 and a couple of weird calculations that I am trying to figure out why they aren't working since others that are similar in data are working just fine.

Does this help?

Report •

July 15, 2010 at 10:05:59
From the Excel Help files on EDATE:

"If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in."

The Help files are our friend.

Report •

Related Solutions

July 15, 2010 at 10:07:09
Thanks for helping. It is now working.
Have a great day!

Report •

July 15, 2010 at 10:28:10

Two issues:
You may not have EDATE() loaded hence a #NAME error.
EDATE is part of the analysis toolpak which comes with Excel 2003, but is not loaded by default - my fault I should have mentioned that.
From the menu bar select Tools - Add-Ins...
Then in the dialog box that opens check the box against Analysis Toolpak - VBA. and click OK.

Try the examples again.

Are you sure that all your dates are recognized by Excel as dates.
Select all cells containing dates and apply a different date format, such as dd/mmm/yyyy
If any of the selected cells does not display a date that looks like this: 01/Jan/2010
then Excel has not recognized it as a date - and any date calculations will not work.

You don't need to use DATEDIF() to get days of difference, as a simple subtraction does it just as well.

Also I don't understand SUM($O4-$R4)
All this does is SUM the result of O4-R4 which is just the value O4-R4

Anyway, try the original proposal when you have Edate working.


Report •

Ask Question