Forum Discussion
Show feb 29th in leap years
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.
I didn't catch exactly how your cells linked by formulas, but you may check if the year leap or not by
=IF(DAY(DATE(G2,2,29))=1,"Not leap","Leap")thus something like
=IF(DAY(DATE(G2,2,29))=1,"","Feb 29")
SergeiBaklan Thanks for the response.
You're thinking the other way around. I'm not trying to determine if the year is a leap year, but I'm trying to show or hide the number 29 (in cell B34) depending on both the month (cell M2) and year/leap year (Cell K2)
The code I have now is: =IF(NOT(M2="februari");29;"")
wich hides the 29 (and 30, 31 in B35 and B36) depending on the name of the month. The formula i'm trying to built now is like this:
=IF(NOT(M2="februari");29;"") UNLESS K2= 2020, 2024, 2028, 2032, 2036 or 2040
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.
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 partExcel 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