Formula to restate a date to the 1st day of the month

[Sundance_Kid]

Hi, I am looking to figure out a formula that can basically restate a date to the 1st day of the month.

I use the format dd/mm/yyyy

So, if I have a file with multiple rows & dates on each row, what formula can restate the date to the 1st day of that month?

Below would be an example of dates to add a formula to restate them.

18/01/2022
22/12/2021
02/04/2021

Thanks

 

[Joe4]

Here is one way (for a date in cell A1):

=EOMONTH(A1,-1)+1

 

[Toadstool]

Hi Sundance_Kid,

It depends if your dates are in text or actual Excel date formats.

Sundance_Kid.xlsx
	A	B	C	D	E	F	G	H
1	Text	Result		Date	Result		…or	Result
2	18/01/2022	01/01/2022		18-Jan-22	01-Jan-22		18-Jan-22	01-Jan-22
3	22/12/2021	01/12/2021		22-Dec-21	01-Dec-21		22-Dec-21	01-Dec-21
4	02/04/2021	01/04/2021		02-Apr-21	01-Apr-21		02-Apr-21	01-Apr-21
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	="01"&MID(A2,3,8)
E2:E4	E2	=DATE(YEAR(D2),MONTH(D2),1)
H2:H4	H2	=EOMONTH(G2,-1)+1

 

[Sundance_Kid]

Here is one way (for a date in cell A1):

=EOMONTH(A1,-1)+1

Thanks, so does EOMONTH(A1,-1) on its own bring you to the end of the current month and then the -1 bring you back to the end of the previous month?

 

[Sundance_Kid]

Hi Sundance_Kid,

It depends if your dates are in text or actual Excel date formats.

Sundance_Kid.xlsx
	A	B	C	D	E	F	G	H
1	Text	Result		Date	Result		…or	Result
2	18/01/2022	01/01/2022		18-Jan-22	01-Jan-22		18-Jan-22	01-Jan-22
3	22/12/2021	01/12/2021		22-Dec-21	01-Dec-21		22-Dec-21	01-Dec-21
4	02/04/2021	01/04/2021		02-Apr-21	01-Apr-21		02-Apr-21	01-Apr-21
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	="01"&MID(A2,3,8)
E2:E4	E2	=DATE(YEAR(D2),MONTH(D2),1)
H2:H4	H2	=EOMONTH(G2,-1)+1

Hi, they are excel date months.

 

[Toadstool]

Hi, they are excel date months.

Then use either the column E or H formulae.

 

[Joe4]

Thanks, so does EOMONTH(A1,-1) on its own bring you to the end of the current month and then the -1 bring you back to the end of the previous month?

See here for a detailed explanation on the function: EOMONTH function

 

[Sundance_Kid]

Thanks both for your help

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Rick Rothstein]

You can also do this without using the EOMONTH function; this seems a little simpler to me...

=A1-DAY(A1)+1