Notifications
Clear all
Topic starter
Attached is a sample of the error I am getting.
Thanks in advance for the help.
Posted : 17/03/2017 10:33 am
Hi,
you could try:
EOMONTH(Q1,-12)
instead of EDATE(...
Referring to the file you shared
=IF(B6=EOMONTH(Q1,-12),B8,SUM(B8:INDEX(B8:Y8,1,MATCH(EOMONTH(Q1,-12),B6:Y6,0))))
Maybe you could get same result (please double check) from
=SUM(B8:INDEX(B8:Y8,MATCH(EOMONTH(Q1,-12),B6:Y6,0)))
Hope that helps
Posted : 17/03/2017 10:47 am
I’ve been looking everywhere for help to my edate error but cant find anything that works. Hope someone here can help.
I am trying to use edate to subtract 12 months from a given date to use in a look up calculation for Fiscal YTD results. Everything works fine until February 2017 rolled around and the results give me February 28, 2016 not February 29, 2016. Which causes a #N/A error.
=IF(B8=EDATE(Q3,-12),B10,SUM(B10:INDEX(B10:Y10,1,MATCH(EDATE(Q3,-12),B8:Y8,0))))
Is the formula I am using which works fine for every other month.
Q3 refers to the current month end date: in this case: 2/28/2017
Row 8 has dates in subsequent cells as follows:
7/31/15 8/31/15 9/30/15 10/31/15 11/30/15 12/31/15 1/31/16 2/29/16 3/30/16 etc all the way up to my 2/28/17 month.
the formula returns: 2/28/16 not the 2/29/16 I need to get my prior fiscal year to date total.
In other words my formula returns the date as 42428 not 42429. Which the causes the formula to error out since there is no 42428 in the date cells.