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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History