# Populate 15th of month or next business day

1. ## Populate 15th of month or next business day

Hi -

My apologies if this a repeat of a thread i started earlier, but I don't see it any where in the forum.

I am creating a somewhat dynamic calendar spreadhseet for a user group that has functions that need to be performed on very specific business days (ex. archive data on business day 10). I've got this working. I have the first day of the month in A1 and in column C the business days are populating. (Thank you Excel Forum!)

The group also has some functions that occur on specific days of the month. I would like to populate the 15th of the month or the next business day if it falls on a weekend, and the last day of the month or the previous business day if it falls on a weekend.

2. ## Re: Populate 15th of month or next business day

=WORKDAY(DATE(2013,1,14),1)
This uses the 14th of the given month and adds 1 business day.

Last day (or previous business day)
=WORKDAY(DATE(2013,2,1),-1)
This uses the 1st day of the NEXT month, then subtracts 1 business day.
So if you want the last day of January, use 2 for the month.
If you want last day of December, use 13 (YES, 13)

3. ## Re: Populate 15th of month or next business day

Originally Posted by Jonmo1
=WORKDAY(DATE(2013,1,14),1)
This uses the 14th of the given month and adds 1 business day.

Last day (or previous business day)
=WORKDAY(DATE(2013,2,1),-1)
This uses the 1st day of the NEXT month, then subtracts 1 business day.
So if you want the last day of January, use 2 for the month.
If you want last day of December, use 13 (YES, 13)
Thank you! Is there a way for this formula to work with the date I have in A1? For example, in this month's calendar I have 8/1/13 in A1, I would like 8/15/13 returned in the first formula and 8/30/13 in the other.

4. ## Re: Populate 15th of month or next business day

Try

=WORKDAY(DATE(YEAR(A1),MONTH(A1),14),1)

Last day (or previous business day)
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)

5. ## Re: Populate 15th of month or next business day

Perfect! Thank you so much

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

#### 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