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/11I 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.

Hi, 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 216The formula in cell C4 is:

=IF(B2<EDATE(B4,-12),B4-EDATE(B4,-12),B4-B2)

The formula in cell C5 is:

=IF(B3>B5,B5-B4,B3-B4)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 yearHere 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 365EDate is a function to get the 'same' date a certain number of months before or after the given date.

Regards

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?

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.

Thanks for helping. It is now working.

Have a great day!

Hi, Two issues:

1.

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.

2.

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.Also:

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-R4Anyway, try the original proposal when you have Edate working.

Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History