# 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/2010New Release Date - 2/01/2011SFY10 - ends 6/30/10SFY11 - 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.

See More: Excel Date Calculations

#1
July 15, 2010 at 08:38:23
 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 216 ```The formula in cell C4 is:=IF(B2B5,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 year2. 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 365```EDate is a function to get the 'same' date a certain number of months before or after the given date.Regards

Report •

#2
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/2011N4 - 6/1/2010 O4 - 313 (number of days between M4 and N4)R1 - 6/30/2010R2 - 365 (days in fiscal year)S1 - 6/30/2011S2 - 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 •

#3
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

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

Report •

#5
July 15, 2010 at 10:28:10
 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/yyyyIf any of the selected cells does not display a date that looks like this: 01/Jan/2010then 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

Report •