Adding 3 sequential months in one Cell based on a date

bearcub

Well-known Member
Joined
May 18, 2005
Messages
624
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
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"))
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
624
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
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. ;)
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
624
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

Like Excel 2010 or 12. Would that use the concatenate function along with the EOM function?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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. ;)
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
624
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Thank you Rick, That makes sense.
 

bearcub

Well-known Member
Joined
May 18, 2005
Messages
624
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Thank you Fluff,
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.

PS please don't forget to update your profile.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,824
Messages
5,544,541
Members
410,619
Latest member
gregor222
Top