Click here for important information about Computing.net.

Solved How to change an Excel formula

Dell / Inspiron 17r
December 24, 2020 at 07:36:11
Specs: windows 10, 2.3/8
The following formula was used to count the number of blank cells in a row for each month in 2020. As 2020 was a leap year, it showed 29 for February when all of the February cells were blank. How can this formula be changed so that it can be used in a non-leap year?

=DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(DATEVALUE($A4&" 1")),1),0))-COUNTA(O4:AS4)

Thank you.
Brian W


See More: How to change an Excel formula


✔ Best Answer
December 24, 2020 at 13:41:12
Maybe I'm missing something, but Excel knows when it's a leap year or not, so I don't think that there is any need to change the formula.

There are 2 ways to test that.

Change your system date to sometime in 2021 and refresh your sheet or change the formula so that YEAR(TODAY()... ends up returning 2021, e.g.

=DAY(EOMONTH(DATE(YEAR(TODAY()+30),MONTH(DATEVALUE($A4&" 1")),1),0))-COUNTA(O4:AS4)

When I do that, the February row returns 28, as expected.

It sure would be inconvenient if every date related formula had to be changed to account/not account for Leap years.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
December 24, 2020 at 09:33:30
What is the value in cell A4?

As just a guess, removing the string:

&" 1"

might be what your looking for.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
December 24, 2020 at 12:51:49
mmcconaghy,

Thank you for your response; unfortunately, it did not work most likely because I didn't give you enough information to work with. Removing &" 1" resulted in an error message that the formula was incorrect.

The "A" column is used for the months of the year: A3 = January; A4 = February; A5 = March; A6 = April; A7 = Mat; A8 = June; A9 = July; A10 = August; A11 = September; A12 =October; A13 = November; A14 = December. In each case the month is spelled out.

I can send you the file if that would help; please let me know.

Thanks again,
Brian W


Reply ↓  Report •

#3
December 24, 2020 at 13:29:50
mmconaghy,

Thank you for your assistance. I was able to resolve the problem by myself.

Thanks again,
Brian W


Reply ↓  Report •

Related Solutions

#4
December 24, 2020 at 13:41:12
✔ Best Answer
Maybe I'm missing something, but Excel knows when it's a leap year or not, so I don't think that there is any need to change the formula.

There are 2 ways to test that.

Change your system date to sometime in 2021 and refresh your sheet or change the formula so that YEAR(TODAY()... ends up returning 2021, e.g.

=DAY(EOMONTH(DATE(YEAR(TODAY()+30),MONTH(DATEVALUE($A4&" 1")),1),0))-COUNTA(O4:AS4)

When I do that, the February row returns 28, as expected.

It sure would be inconvenient if every date related formula had to be changed to account/not account for Leap years.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
December 25, 2020 at 15:50:55
DerbyDad03,

Thank you for your response. I used your tests and everything worked out correctly.

Thanks again,
Brian W


Reply ↓  Report •

Ask Question