+ Reply to Thread
Results 1 to 4 of 4

most recent month-end, quarter-end and year-end workday, non-holiday dates

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    5

    most recent month-end, quarter-end and year-end workday, non-holiday dates

    Hi,

    Spent most of the morning on this and could not get it to work properly

    I have a date in cell A1 which is today's date. In B1:B10 I have a list of holiday dates that have been manually entered.

    1/8/2018 =today()
    1/15/2018 manual entries
    2/19/2018
    ...
    12/25/2018

    For the most recent month end, the formula =WORKDAY(DATE(YEAR(A1),MONTH(A1),1),-1,B1:B10) works great!

    Problem:
    I need the most recent quarter-end and year-end workday and non-holiday dates to display, relative to the date in A1. I also need a 3 year ago, month-end date. Please help.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: most recent month-end, quarter-end and year-end workday, non-holiday dates

    I assume that your quarters end at the end of March, June, September and December?

    If so try this formula

    =WORKDAY(DATE(YEAR(A1),FLOOR(MONTH(A1),3)+1,1),-1,B1:B10)

    For most recent month end this is shorter

    =WORKDAY(A1-DAY(A1)+1,-1,B1:B10)

    For year end

    =WORKDAY(DATE(YEAR(A1),1,1),-1,B1:B10)

    For 3 year ago month end try this:

    =WORKDAY(EDATE(A1-DAY(A1)+1,-36),-1,B1:B10)
    Last edited by daddylonglegs; 01-08-2018 at 03:37 PM.
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: most recent month-end, quarter-end and year-end workday, non-holiday dates

    Quote Originally Posted by daddylonglegs View Post
    I assume that your quarters end at the end of March, June, September and December?

    If so try this formula

    =WORKDAY(DATE(YEAR(A1),FLOOR(MONTH(A1),3)+1,1),-1,B1:B10)

    For most recent month end this is shorter

    =WORKDAY(A1-DAY(A1)+1,-1,B1:B10)

    For year end

    =WORKDAY(DATE(YEAR(A1),1,1),-1,B1:B10)

    For 3 year ago month end try this:

    =WORKDAY(EDATE(A1-DAY(A1)+1,-36),-1,B1:B10)
    You're the best! Thank you this is very helpful.

  4. #4
    Registered User
    Join Date
    10-04-2022
    Location
    Zurich
    MS-Off Ver
    M365
    Posts
    3

    Re: most recent month-end, quarter-end and year-end workday, non-holiday dates

    Hey there, this was already very helpful, thank you. I have a follow up question:

    I am using: =WORKDAY(EOMONTH(G5,2-MOD(MONTH(G5)-1,3))+1,-1,(Calc!G4:G16))

    I am trying to show quarterly payment dates for a mortgage with a 24 months duration. However, if my first payment date is 30.12.2022, the next last day of a quarter will be shown as 30.12.22 instead of 31.03.2023. How can I adapt the formula to calculate over a year?

    I hope the above makes sense!

    Many thanks in advance!

    Best,
    Manuel

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to prevent pivot table breaking down dates (Month, Quarter, Year, etc)
    By loccy in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-28-2017, 01:44 AM
  2. How to sum data by month, by quarter, by year?
    By kevinSHR in forum Excel General
    Replies: 3
    Last Post: 05-06-2016, 04:06 PM
  3. [SOLVED] Using SumProduct for dates inclusive of Year to Date, Month to date, Quarter to Date
    By cartica in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2014, 04:22 PM
  4. Expanding 4th quarter by month ONLY for certain year
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-28-2013, 06:13 PM
  5. [SOLVED] PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month
    By bimmer5dude in forum Excel General
    Replies: 5
    Last Post: 06-30-2012, 09:13 PM
  6. Workday and Vlookup to select a range of holiday dates
    By vadius in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2011, 10:28 AM
  7. Find First Workday of Month Value Excl Holiday
    By the.ronin in forum Excel General
    Replies: 8
    Last Post: 04-14-2011, 10:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1