How do you calculate the number of first days of the month have passed between a set date and today

[damian_r_Home]

Morning All.
THis should be simple (I think) but lost as to how to do it.

A1 contains a date, eg 1st April 2023
A2 is =TODAY()
A3 needs to show how many 1st's of a month have passed by between A1 and A2.

What would be really clever is if in cell A4 we could put 1, 2, 3, 4 etc to represent the 1st, 2nd, 3rd, 4th etc of the month and A3 could then return how many of them have passed between A1 and A2

As always, your help is appreciated.

D

 

[awoohaw]

are you using 365 or 2007 for this question?

for 365:

Mr Excel Questions 75.xlsm
	A
1	4/2/2023
2	3/19/2024
3	11
Sheet16

Cell Formulas
Range	Formula
A2	A2	=TODAY()
A3	A3	=SUM(--(DAY(SEQUENCE(A2-A1,1,A1,1))=1))

 

[awoohaw]

I just realized what I gave above does not include the last day if it s the first of the month, use the green highlighted formula instead:

Mr Excel Questions 75.xlsm
	A	B	C	D
1	4/1/2023	4/2/2023	4/1/2023	4/2/2023
2	3/19/2024	3/19/2024	4/1/2024	4/1/2024
3	12	11	13	12
Sheet16

Cell Formulas
Range	Formula
A2:B2	A2	=TODAY()
C2:D2	C2	=TODAY()+13
A3:B3	A3	=SUM(--(DAY(SEQUENCE(A2-A1,1,A1,1))=1))
C3:D3	C3	=SUM(--(DAY(SEQUENCE(C2-C1,1,C1,1))=1))+(DAY(C2)=1)

You can also use the =DATEDIF Function (Also not available in 2007) Although this is for LOTUS 1-2-3 compatibility and is not always reliable for some arguments.

Mr Excel Questions 75.xlsm
	A	B	C	D
1	4/1/2023	4/2/2023	4/1/2023	4/2/2023
2	3/19/2024	3/19/2024	4/1/2024	4/1/2024
3	12	11	13	12
4			13	12
Sheet16

Cell Formulas
Range	Formula
A2:B2	A2	=TODAY()
A3:B3	A3	=SUM(--(DAY(SEQUENCE(A2-A1,1,A1,1))=1))
C2:D2	C2	=TODAY()+13
C3:D3	C3	=SUM(--(DAY(SEQUENCE(C2-C1,1,C1,1))=1))+(DAY(C2)=1)
C4:D4	C4	=DATEDIF(C1,C2,"m")+(DAY(C2)=1)

 

[shift-del]

Hi

Mappe2
	A	B	C	D
1	01.04.2023		1	12
2	19.03.2024		2	12
3			3	12
4			4	12
5			5	12
6			6	12
7			7	12
Tabelle1

Cell Formulas
Range	Formula
C1:D32	C1	=LET( d,DAY(SEQUENCE(A2-A1,,A1)), d_m,SEQUENCE(31), f,FREQUENCY(d,d_m), result,HSTACK(d_m,f), result)
A2	A2	=TODAY()
Dynamic array formulas.

 

[damian_r_Home]

Morning.

Thanks for your help folks, I'll try these today.