TRC is primarily funded by ad revenue. If you like the content you find here, don't block the ads check them out instead. Thank you.

# Thread: Excel question, 5Y forward date calc.

1. ## Excel question, 5Y forward date calc.

TRC is primarily funded by ad revenue. If you like
the content you find here, don't block the ads check
Tricky one. In a spreadsheet I have a date. I want to calc 5y forward of that date (easy!) but only UK working days (not so easy!). Any clues as to whether there's a set formula for this or will I have to mumble-swerve?

2. ## Re: Excel question, 5Y forward date calc.

Does Excel have 'NETWORKDAYS(DATE_1,DATE_2)' ?? (I know google sheets has it cos I used it recently)

3. ## Re: Excel question, 5Y forward date calc.

Originally Posted by RiceBurner
Does Excel have 'NETWORKDAYS(DATE_1,DATE_2)' ?? (I know google sheets has it cos I used it recently)
The lad's good, but not quite there....

NETWORKSDAYS allows you to calc the number of business days between from date and to date. It'll give you the number of business days, but won't calc an end date from the start. I'm looking for something that will give me:

STARTDATE+5Y, with the caveat that the calculated end date has to be a business day*

*We then get into the reals of public holidays, roll conventions, etc., but let's get the basics sorted first.

4. ## Re: Excel question, 5Y forward date calc.

I thought you had oiks to do real work for you ?

5. ## Re: Excel question, 5Y forward date calc.

Did you want, if the result isn't a working day, the next working day after or the last working day?

If the latter, possibly:
=WORKDAY(EOMONTH(B1,59)+DAY(B1),5)-7
where B1 is the start date.

6. ## Re: Excel question, 5Y forward date calc.

Originally Posted by Yorick
I thought you had oiks to do real work for you ?
Every once in a while I have to remind people of my rare genius.....

7. ## Re: Excel question, 5Y forward date calc.

Originally Posted by Kneerly Down
Did you want, if the result isn't a working day, the next working day after or the last working day?

If the latter, possibly:
=WORKDAY(EOMONTH(B1,59)+DAY(B1),5)-7
where B1 is the start date.
Welcome to the murky world of day count conventions.....

I'm after Modified Following day convention, whereby the date rolls forward to the next good business day, unless it would roll into the next month, in which case it rolls back to the previous good day.

8. ## Re: Excel question, 5Y forward date calc.

Would need to check for leap year and also won't account for non-weekend holiday days but:

=EOMONTH(B1,59)+DAY(B1)+IF(WEEKDAY(EOMONTH(B1,59)+ DAY(B1),16)=1,IF(DAY(EOMONTH(B1,59))-DAY(B1)<2,-1,2),IF(WEEKDAY(EOMONTH(B1,59)+DAY(B1),16)=2,IF(DA Y(EOMONTH(B1,59))=DAY(B1),-2,1),0))

9. ## Re: Excel question, 5Y forward date calc.

Would be easier doing a function rather than formula tbh.

10. ## Re: Excel question, 5Y forward date calc.

Originally Posted by Kneerly Down
Would be easier doing a function rather than formula tbh.
I could have done it for him when I was still working. But brain dead now

11. ## Re: Excel question, 5Y forward date calc.

Oh, and if using a 'newer' version of Excel use EDATE, so:
=EDATE(B1,60)+IF(WEEKDAY(EDATE(B1,60),16)=1,IF(DAY (EOMONTH(B1,60))-DAY(B1)<2,-1,2),IF(WEEKDAY(EDATE(B1,60),16)=2,IF(DAY(EOMONTH( B1,60))=DAY(B1),-2,1),0))

where 'newer' is since Excel 2007 IIRC

CORRECTED. EOMONTH should have used 60 all along, including the above, I think. Bit rusty on this!
Still might not be right for Leap Year Febs.
Anyway, get one of your oiks to do the checking!