Days in Specific month

[jswillcox]

I would like to have a formula that will show the progress of days in a specific month.
Example: On 10 Jan 2017 the formula would create the number 10.

The tricky part is I would like it to stay at the total days in the given month when it goes to the next.
Example: On 01 Feb 2017 it would stay at 31 until next January.

In addition I would like a separate formula that would show the total number of days in a given month. So the only number that would change would be February in a leap year.
Example: 05 Feb 2016 it would create the number 29. 05 Feb 2017 it would create the number 28.

Thank you for your help!

 

[wiseone]

Luckily, excel uses a number formatting for dates where 1 day = 1.

So if you format the cells you are working with to dates, all you have to do is subtract one from the other and you will get your total number of days.
For example:
*All cells with dates formatted as dates.
A1 = Jan 1, 2017 = (42736.00, this is what excel uses as a date number convention, the .00 is for if specify a time within the day))
A2 = Feb 5, 2017 = (42771.00)

B1 = A2-A1 = 35

Excel automatically understand which years are leap years, so you don't need to include any additional calculations to account for them.

Did this answer your question?

If not, Im a little fuzzy on what you meant by:

The tricky part is I would like it to stay at the total days in the given month when it goes to the next.

I think you may need something in your calculation which shows today's date? in which case you can use the function =Today() in a cell and an if statement to determine whether to update the cell....

For example:

A1 = Today()
A2 = Jan 1 2017
A3 = Feb 5, 2017

B3 = If(A3 = A1, A3-A1, A3-A2)

Need a little more clarification on what you're looking for if this didn't do it.

Good luck!

 

[barry houdini]

To get the number of days in the current month

=DAY(EOMONTH(TODAY(),0))

or you can apply that to a date in a cell rather than today, e.g.

=DAY(EOMONTH(A1,0))

 

[jswillcox]

To get the number of days in the current month

=DAY(EOMONTH(TODAY(),0))

or you can apply that to a date in a cell rather than today, e.g.

=DAY(EOMONTH(A1,0))

This is close to what I am looking for. I want to keep a sheet for January. So I would like the formula to stay with Januarys days when the next month starts. So it will stay at 31 from 01 Feb - 31 Dec. This will only reset when it is January next year.

So it will show the progress through the month (02 Jan it shows 2, 11 Jan it shows 11, ect) but after January it stays at 31.

I hope I explained that well. If not please ask for additional clarification.

 

[jswillcox]

Luckily, excel uses a number formatting for dates where 1 day = 1.

So if you format the cells you are working with to dates, all you have to do is subtract one from the other and you will get your total number of days.
For example:
*All cells with dates formatted as dates.
A1 = Jan 1, 2017 = (42736.00, this is what excel uses as a date number convention, the .00 is for if specify a time within the day))
A2 = Feb 5, 2017 = (42771.00)

B1 = A2-A1 = 35

Excel automatically understand which years are leap years, so you don't need to include any additional calculations to account for them.

Did this answer your question?

If not, Im a little fuzzy on what you meant by:


I think you may need something in your calculation which shows today's date? in which case you can use the function =Today() in a cell and an if statement to determine whether to update the cell....

For example:

A1 = Today()
A2 = Jan 1 2017
A3 = Feb 5, 2017

B3 = If(A3 = A1, A3-A1, A3-A2)

Need a little more clarification on what you're looking for if this didn't do it.

Good luck![/COLOR]

I am looking to have this updated automatically so we don't have to add any dates into cells using somthing to the effect of today(). Can your formula work automatically that way?

 

[Scott T]

I think this is what you want. You will need to change the two 1 to the appropriate number for the month your are using 1 for Jan 2 for Feb etc

=IF(MONTH(TODAY())=[COLOR=#FF0000]1[/COLOR],DAY(TODAY()),DAY(EOMONTH(DATE(YEAR(TODAY()),1,[COLOR=#FF0000]1[/COLOR]),0)))

 

[jswillcox]

I think this is what you want. You will need to change the two 1 to the appropriate number for the month your are using 1 for Jan 2 for Feb etc

=IF(MONTH(TODAY())=[COLOR=#ff0000]1[/COLOR],DAY(TODAY()),DAY(EOMONTH(DATE(YEAR(TODAY()),1,[COLOR=#ff0000]1[/COLOR]),0)))

This is what I need for the end of the month. I just need one more equation that will do this same thing but put the day in the month. So it will show the current day of the month but when it moves to the next month it shows the same as this current equation.

 

[jswillcox]

I think this is what you want. You will need to change the two 1 to the appropriate number for the month your are using 1 for Jan 2 for Feb etc

=IF(MONTH(TODAY())=[COLOR=#ff0000]1[/COLOR],DAY(TODAY()),DAY(EOMONTH(DATE(YEAR(TODAY()),1,[COLOR=#ff0000]1[/COLOR]),0)))

Correction. This formula works for what I need. I was not changing the first number you had hilighted. Thank you for this!