Forum Discussion

Ebbulk's avatar
Ebbulk
Copper Contributor
Oct 01, 2019
Solved

Show feb 29th in leap years

Hi, I have a series of numbers (B6 till B36) containing numbers 1 till 31 for the days of the month. The last cell is dependent on cell M2 containing the word "januari, march, may...etc" for showing ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 04, 2019

    Ebbulk 

    If I understood you correctly your data is like this

    In B6 you may use

    =IF((MONTH(DATE($K$2,MONTH(1&LEFT($M$2,3)),ROW()-ROW($B$5)))=MONTH(1&LEFT($M$2,3))),ROW()-ROW($B$5),"")

    and drag it down till B36. It works for any correct combination of year and month in K2 and M2 and skips 29, 30, 31 if there are no such days in year and month.

Ebbulk's avatar
Ebbulk
Copper Contributor
Oct 04, 2019

SergeiBaklan It works! You're my hero. 
Can you help me understand how this formula works?
You make use of the row numbers, but I don't understand the month(1&left(M2;3 part

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 04, 2019

Ebbulk 

Excel automatically converts date & month combination into the date for the current year. If we take DATEVALUE() which converts text to date with

=DATEVALUE("1February")
or
=DATEVALUE("1Feb")

Excel converts above into the date, 01st February 2019 (2019 is the current year).

If wrap above with MONTH as

=MONTH(DATEVALUE("1February"))

result will be 2 (second month of the year).  But with such wrapping we may skip DATEVALUE(), conversion will be applied automatically under the MONTH(). And I'd prefer to use 3 first letters for month's names to reduce the risk of the misprints. Finally

=MONTH("1Feb") returns 2
=MONTH("1Jul") returns 7
etc

 

  • Ebbulk's avatar
    Ebbulk
    Copper Contributor
    Oct 04, 2019

    Awesome. Thanks loads!