Share via

Very specific add date formula

Anonymous
2024-02-01T11:38:25+00:00

Hi, I'm trying to create a budgeting spreadsheet and using the basic add 1 month formula ( "=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))" ), but I'd like a formula to add a month to the date which excludes weekends and holidays, but also takes into account those that fall on the end of the month which is not a standard date in the following month please. i.e. Any month bill that falls on the 29/30th or 31st and the following month doesn't have this date (particularly in Februarys' case.) Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

2 answers

Sort by: Most helpful
  1. Anonymous
    2024-02-01T18:58:23+00:00

    Excel 365 Pro Plus with Power Query.

    Tables for Jan, Feb, and Mar that list all their dates,

    excluding weekends and holidays.

    No formulas, no VBA macro.

    https://www.mediafire.com/file_premium/llnvz6c53sf547i/02_01_24.xlsx/file

    https://www.mediafire.com/file_premium/ryj23gvaumf9qyw/02_01_24.pdf/file

    Was this answer helpful?

    0 comments No comments
  2. HansV 462.6K Reputation points
    2024-02-01T13:46:48+00:00

    Does this do what you want?

    =WORKDAY(EDATE(A1, 1)-1, 1, Holidays)

    where Holidays is a named range that contains the public holidays that you want to exclude.

    Was this answer helpful?

    0 comments No comments