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.
Results 1 to 11 of 11

Thread: Excel question, 5Y forward date calc.

  1. #1
    Should Get Out More gremlin's Avatar
    Join Date
    Aug 2008
    Location
    Eating the last biscuit with the Daily Mail reader.
    Posts
    16,673
    Like
    520
    Liked 4,678 in 2,329 posts

    Default 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
    them out instead. Thank you.
    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. #2
    Should Get Out More RiceBurner's Avatar
    Join Date
    Jan 2008
    Location
    Hiding in your blind spot...
    Posts
    14,933
    Like
    4,039
    Liked 1,506 in 875 posts

    Default 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. #3
    Should Get Out More gremlin's Avatar
    Join Date
    Aug 2008
    Location
    Eating the last biscuit with the Daily Mail reader.
    Posts
    16,673
    Like
    520
    Liked 4,678 in 2,329 posts

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

    Quote Originally Posted by RiceBurner View Post
    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. #4
    Should Get Out More Yorick's Avatar
    Join Date
    Jan 2008
    Location
    Lanzarote
    Posts
    39,945
    Like
    6,315
    Liked 6,066 in 3,806 posts
    Blog Entries
    6

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

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

  5. #5
    Should Get Out More Kneerly Down's Avatar
    Join Date
    Jan 2008
    Location
    Highlands
    Posts
    9,580
    Like
    643
    Liked 1,430 in 876 posts

    Default 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. #6
    Should Get Out More gremlin's Avatar
    Join Date
    Aug 2008
    Location
    Eating the last biscuit with the Daily Mail reader.
    Posts
    16,673
    Like
    520
    Liked 4,678 in 2,329 posts

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

    Quote Originally Posted by Yorick View Post
    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. #7
    Should Get Out More gremlin's Avatar
    Join Date
    Aug 2008
    Location
    Eating the last biscuit with the Daily Mail reader.
    Posts
    16,673
    Like
    520
    Liked 4,678 in 2,329 posts

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

    Quote Originally Posted by Kneerly Down View Post
    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. #8
    Should Get Out More Kneerly Down's Avatar
    Join Date
    Jan 2008
    Location
    Highlands
    Posts
    9,580
    Like
    643
    Liked 1,430 in 876 posts

    Default 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. #9
    Should Get Out More Kneerly Down's Avatar
    Join Date
    Jan 2008
    Location
    Highlands
    Posts
    9,580
    Like
    643
    Liked 1,430 in 876 posts

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

    Would be easier doing a function rather than formula tbh.

  10. #10
    Should Get Out More Yorick's Avatar
    Join Date
    Jan 2008
    Location
    Lanzarote
    Posts
    39,945
    Like
    6,315
    Liked 6,066 in 3,806 posts
    Blog Entries
    6

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

    Quote Originally Posted by Kneerly Down View Post
    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. #11
    Should Get Out More Kneerly Down's Avatar
    Join Date
    Jan 2008
    Location
    Highlands
    Posts
    9,580
    Like
    643
    Liked 1,430 in 876 posts

    Default 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!

Go Back to Forum My Forum

TRC Affiliates - Help TRC make a small amount of commission