SOLVED
Home

Show feb 29th in leap years

%3CLINGO-SUB%20id%3D%22lingo-sub-885701%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417969%22%20target%3D%22_blank%22%3E%40Ebbulk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20catch%20exactly%20how%20your%20cells%20linked%20by%20formulas%2C%20but%20you%20may%20check%20if%20the%20year%20leap%20or%20not%20by%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(DAY(DATE(G2%2C2%2C29))%3D1%2C%22Not%20leap%22%2C%22Leap%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ethus%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(DAY(DATE(G2%2C2%2C29))%3D1%2C%22%22%2C%22Feb%2029%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892867%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20response.%26nbsp%3B%3CBR%20%2F%3EYou're%20thinking%20the%20other%20way%20around.%20I'm%20not%20trying%20to%20determine%20if%20the%20year%20is%20a%20leap%20year%2C%20but%20I'm%20trying%20to%20show%20or%20hide%20the%20number%2029%20(in%20cell%20B34)%20depending%20on%20both%20the%20month%20(cell%20M2)%20and%20year%2Fleap%20year%20(Cell%20K2)%3CBR%20%2F%3EThe%20code%20I%20have%20now%20is%3A%26nbsp%3B%3DIF(NOT(M2%3D%22februari%22)%3B29%3B%22%22)%3CBR%20%2F%3Ewich%20hides%20the%2029%20(and%2030%2C%2031%20in%20B35%20and%20B36)%20depending%20on%20the%20name%20of%20the%20month.%20The%20formula%20i'm%20trying%20to%20built%20now%20is%20like%20this%3A%3CBR%20%2F%3E%3DIF(NOT(M2%3D%22februari%22)%3B29%3B%22%22)%20UNLESS%20K2%3D%202020%2C%202024%2C%202028%2C%202032%2C%202036%20or%202040%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885669%22%20slang%3D%22en-US%22%3EShow%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885669%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20series%20of%20numbers%20(B6%20till%20B36)%20containing%20numbers%201%20till%2031%20for%20the%20days%20of%20the%20month.%20The%20last%20cell%20is%20dependent%20on%20cell%20M2%20containing%20the%20word%20%22januari%2C%20march%2C%20may...etc%22%20for%20showing%2031%20or%20staying%20empty.%20Cell%20B34%20contains%20a%20simulair%20formula%20for%20M2%20containing%20the%20word%20%22februari%22.%20Works%20perfectly....%3CBR%20%2F%3EExept%20for%20leap%20years.%20Years%20are%20located%20in%20K2.%3CBR%20%2F%3EHow%20can%20I%20built%20a%20formula%20containing%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3Eif%3CSTRONG%3E%26nbsp%3BM2%3C%2FSTRONG%3E%20is%20%3CSTRONG%3Enot%20%22februari%22%3B29%20unless%20K2%3C%2FSTRONG%3E%20contains%20%3CSTRONG%3E%222020%22%2C%20%222024%22%2C%20%222028%22%2C%20%222032%22%2C%20%222036%22%20or%20%222040%22%3C%2FSTRONG%3E%20(20%20years%20in%20the%20future%20should%20be%20sufficiant)%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20help!!%3CBR%20%2F%3E%3CBR%20%2F%3EErwin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-885669%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893172%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417969%22%20target%3D%22_blank%22%3E%40Ebbulk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20you%20correctly%20your%20data%20is%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20395px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135418i88FF7F1159A7673A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20B6%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF((MONTH(DATE(%24K%242%2CMONTH(1%26amp%3BLEFT(%24M%242%2C3))%2CROW()-ROW(%24B%245)))%3DMONTH(1%26amp%3BLEFT(%24M%242%2C3)))%2CROW()-ROW(%24B%245)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20till%20B36.%20It%20works%20for%20any%20correct%20combination%20of%20year%20and%20month%20in%20K2%20and%20M2%20and%20skips%2029%2C%2030%2C%2031%20if%20there%20are%20no%20such%20days%20in%20year%20and%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893200%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BIt%20works!%20You're%20my%20hero.%26nbsp%3B%3CBR%20%2F%3ECan%20you%20help%20me%20understand%20how%20this%20formula%20works%3F%3CBR%20%2F%3EYou%20make%20use%20of%20the%20row%20numbers%2C%20but%20I%20don't%20understand%20the%20month(1%26amp%3Bleft(M2%3B3%20part%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893234%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417969%22%20target%3D%22_blank%22%3E%40Ebbulk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20automatically%20converts%20date%20%26amp%3B%20month%20combination%20into%20the%20date%20for%20the%20current%20year.%20If%20we%20take%20DATEVALUE()%20which%20converts%20text%20to%20date%20with%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DDATEVALUE(%221February%22)%0Aor%0A%3DDATEVALUE(%221Feb%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EExcel%20converts%20above%20into%20the%20date%2C%2001st%20February%202019%20(2019%20is%20the%20current%20year).%3C%2FP%3E%0A%3CP%3EIf%20wrap%20above%20with%20MONTH%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMONTH(DATEVALUE(%221February%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eresult%20will%20be%202%20(second%20month%20of%20the%20year).%26nbsp%3B%20But%20with%20such%20wrapping%20we%20may%20skip%20DATEVALUE()%2C%20conversion%20will%20be%20applied%20automatically%20under%20the%20MONTH().%20And%20I'd%20prefer%20to%20use%203%20first%20letters%20for%20month's%20names%20to%20reduce%20the%20risk%20of%20the%20misprints.%20Finally%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMONTH(%221Feb%22)%20returns%202%0A%3DMONTH(%221Jul%22)%20returns%207%0Aetc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893252%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893252%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome.%20Thanks%20loads!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893352%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20feb%2029th%20in%20leap%20years%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417969%22%20target%3D%22_blank%22%3E%40Ebbulk%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ebbulk
New Contributor

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 31 or staying empty. Cell B34 contains a simulair formula for M2 containing the word "februari". Works perfectly....
Exept for leap years. Years are located in K2.
How can I built a formula containing this:

if M2 is not "februari";29 unless K2 contains "2020", "2024", "2028", "2032", "2036" or "2040" (20 years in the future should be sufficiant)

Thanks for the help!!

Erwin

7 Replies

@Ebbulk 

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")

 

@Sergei Baklan 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

Solution

@Ebbulk 

If I understood you correctly your data is like this

image.png

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.

@Sergei Baklan 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

@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

 

Awesome. Thanks loads!

@Ebbulk , you are welcome

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies