Adding 3 sequential months in one Cell based on a date

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I have situation where I have new people who are paid monthly draws for the first three months of their employment.

What I would like to do is to have a formula that will populate one cell with EOM dates to remind me when to include them in payroll.

I just missed a couple of draws last month because I thought the rep's first payment was to be in Oct. I realized after the fact it was supposed to start in Sept.

So I thought, if it was possible, is to have a formula that I could use that would populate the first 3 pay dates. The result in the formula cell should be the first EOM date after their hire month.

Is this possible?

I know that I could have these month end dates populates over 3 rows in an array but I would rather have them in one cell if possible.

Example:

Criteria (Cell B3)

State date: 8/20/2020

Result (all dates are one cell):
9/30/2020, 10/31/2020,, 11/30/2020

Thank you in Advance,

Michael
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
If you have dynamic array functions try
Excel Formula:
=TEXTJOIN(", ",,TEXT(EOMONTH(B3,SEQUENCE(3)),"dd/mm/yyyy"))
 
Upvote 0
I do have Office 365. thank you.

Works like a gem! Is this part of the new Array formulas that just came out?

What would be the equivalent formula in prior versions of Excel - just out of curiosity
 
Upvote 0
Sequence is one of the dynamic array functions, the others aren't
What would be the equivalent formula in prior versions of Excel
Depends which versions you are talking about. ;)
 
Upvote 0
Like Excel 2010 or 12. Would that use the concatenate function along with the EOM function?
 
Upvote 0
If you didn't have a version of Excel with SEQUENCE in it, and since you are only interested in 3 months worth of values, you could have done it this way...

=TEXT(EOMONTH(B3,1),"mm/dd/yyyy, ")&TEXT(EOMONTH(B3,2),"mm/dd/yyyy, ")&TEXT(EOMONTH(B3,3),"mm/dd/yyyy")
 
Upvote 0
For 2010 you would have to do it individually like
Excel Formula:
=TEXT(EOMONTH(B3,1),"dd/mm/yyyy")&", "&TEXT(EOMONTH(B3,2),"dd/mm/yyyy")
Also please don't forget to update your account details & then scroll down & click save. ;)
 
Upvote 0
Thank you Rick, That makes sense.
 
Upvote 0
Glad we could help & thanks for the feedback.

PS please don't forget to update your profile.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top