Calculate date Formula

[thescream80]

I am trying to calculate number of days per month without having to change the date every day and it to only calculate to the days within THAT month.

A1 =TODAY()
A2 =2/1/22

To get today's date updated automatically i am using this formula =TODAY()
To get current day count from the beginning of the month I subtract =A1-A2

Is that a way to Max A1 so it does not count the days past 2/28, or 3/31 ect

=TODAY()	Number of Days of Current month
=2/1/22	=A1-A2
	Need the above to not count the days after the end of the month

Thank you for any help

 

[StephenCrump]

It's not clear what results you're after here?

... but: =DAY(EOMONTH(TODAY(),0)) tells you that there are 28 days in this month.

 

[thescream80]

It's not clear what results you're after here?

... but: =DAY(EOMONTH(TODAY(),0)) tells you that there are 28 days in this month.

Hi Stephen!

What I am trying to achieve is to have a formula that I will apply to every month and for it to count the days in that month in real time yet cap the number at the end of the month.
For example using today Feb 16th

January formula = 31
February = 16 (tomorrow 17)

When the date is March 1st though Feb=28 and March =1


Does that make more sense now?

 

[StephenCrump]

Here's one way you could do it (there are probably several others)

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22
3		31	17	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2:M2	B2	=EDATE(DATE(YEAR(TODAY()),1,1),COLUMNS($B2:B2)-1)
B3:M3	B3	=MAX(0,1+MIN(TODAY(),EOMONTH(B2,0))-B2)

 

[bebo021999]

I am trying to calculate number of days per month without having to change the date every day and it to only calculate to the days within THAT month.

=DAY(A1)
Is it are you looking for?

 

[thescream80]

=DAY(A1)
Is it are you looking for?

So I think I ran into an issue

Your solution works for that month. But when the next month happens it will reset the days. I am trying to find a formula that will count the existing month days in real time but when the next month starts it will keep the days already counted.
Example

2022 - 47 (as of feb 16th)
Jan - 31
Feb - 16 (as of feb 16th)
March
April
May
June
July
August
September
October
November
December

 

[bebo021999]

I think that might be it. One question is how could i add a modification to also track it for the year?

Not clear for me.
If today is 17-Feb, it returns 17
If today is 31-Jan, it returns 31

Provide more specific sample, with your desired outcome.

 

[thescream80]

Not clear for me.
If today is 17-Feb, it returns 17
If today is 31-Jan, it returns 31

Provide more specific sample, with your desired outcome.

So I think I ran into an issue

Your solution works for that month. But when the next month happens it will reset the days. I am trying to find a formula that will count the existing month days in real time but when the next month starts it will keep the days already counted.

Example each of the following will be in a different cell. - I want to count the days real time while in that month BUT when the month or year is over it counts the max days for that time period that already has passed. see below

2022 - 47 (as of feb 16th)
Jan - 31
Feb - 16 (as of feb 16th)
March - 0
April- 0
May- 0
June- 0
July- 0
August- 0
September- 0
October- 0
November- 0
December- 0
2023- 0

 

[bebo021999]

Does #4 work? If not, what results should be?

 

[thescream80]

Here's one way you could do it (there are probably several others)

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Jan 22	Feb 22	Mar 22	Apr 22	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22
3		31	17	0	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2:M2	B2	=EDATE(DATE(YEAR(TODAY()),1,1),COLUMNS($B2:B2)-1)
B3:M3	B3	=MAX(0,1+MIN(TODAY(),EOMONTH(B2,0))-B2)

Sorry I missed this one earlier!
Is there a way to add a formula for the year as well?