SOLVED
Home

Test if day of month falls between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-772430%22%20slang%3D%22en-US%22%3ETest%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772430%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20trying%20to%20relay%20a%20month-month%20formula%20in%20Excel%202013.%20Where%20if%20a%20Date%20value%20is%20inputted%20in%20a%20cell%2C%20another%20cell%20(with%20formula)%20would%20display%20%221st%20date%20of%20month-last%20date%20of%20month%22%20i.e.%20-%20if%20A1%20reads%20%224-1-19%22%20A2%20would%20read%20%224%2F1%2F19-4%2F30%2F19%22.%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20A1%20reads%20%225-1-19%22%20A2%20would%20read%20%225%2F1%2F19-5%2F31%2F19%22%20so%20forth%20and%20so%20on...%3C%2FP%3E%3CP%3EIs%20this%20even%20a%20function%20in%20Excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772430%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772478%22%20slang%3D%22en-US%22%3ERe%3A%20Test%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381709%22%20target%3D%22_blank%22%3E%40nvega%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DTEXT(EOMONTH(A1%2C-1)%2B1%2C%22m%2Fd%2Fyy%22)%20%26amp%3B%20%22%20-%20%22%20%26amp%3B%20TEXT(EOMONTH(A1%2C0)%2C%22m%2Fd%2Fyy%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772577%22%20slang%3D%22en-US%22%3ERe%3A%20Test%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381709%22%20target%3D%22_blank%22%3E%40nvega%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3CBR%20%2F%3EYou%20can%20use%20one%20of%202%20functions%20(EOMONTH%20or%20DATE)%20in%20combination%20with%20a%20TEXT%20function%20to%20set%20the%20formatting%20you%20want.%20Since%20the%20EOMONTH%20has%20been%20already%20provided%2C%20then%20here%20is%20the%20other%20one%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20formula%3C%2FP%3E%3CP%3E%3DTEXT(DATE(YEAR(A1)%2CMONTH(A1)%2C1)%2C%22mm%2Fdd%2Fyy%22)%26amp%3B%3C%2FP%3E%3CP%3E%22%20-%20%22%26amp%3BTEXT(DATE(YEAR(A1)%2CMONTH(A1)%2B1%2C0)%2C%22mm%2Fdd%2FYY%22)%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774040%22%20slang%3D%22en-US%22%3ERe%3A%20Test%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774040%22%20slang%3D%22en-US%22%3E%3CP%3Eworked!%20thanks%20a%20million%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774050%22%20slang%3D%22en-US%22%3ERe%3A%20Test%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381709%22%20target%3D%22_blank%22%3E%40nvega%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20glad%20I%20could%20help...%20Good%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774051%22%20slang%3D%22en-US%22%3ERe%3A%20Test%20if%20day%20of%20month%20falls%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774051%22%20slang%3D%22en-US%22%3E%3CP%3Eworked%20also%2C%20thank%20you!%26nbsp%3B%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%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
nvega
Occasional Contributor

I've been trying to relay a month-month formula in Excel 2013. Where if a Date value is inputted in a cell, another cell (with formula) would display "1st date of month-last date of month" i.e. - if A1 reads "4-1-19" A2 would read "4/1/19-4/30/19". 

if A1 reads "5-1-19" A2 would read "5/1/19-5/31/19" so forth and so on...

Is this even a function in Excel?

5 Replies

@nvega 

It could be

=TEXT(EOMONTH(A1,-1)+1,"m/d/yy") & " - " & TEXT(EOMONTH(A1,0),"m/d/yy")
Solution

@nvega 

Hi
You can use one of 2 functions (EOMONTH or DATE) in combination with a TEXT function to set the formatting you want. Since the EOMONTH has been already provided, then here is the other one

You can use the formula

=TEXT(DATE(YEAR(A1),MONTH(A1),1),"mm/dd/yy")&

" - "&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"mm/dd/YY")

Hope that helps

Nabil Mourad

worked! thanks a million @nabilmourad 

@nvega 

I'm glad I could help... Good Luck

Nabil Mourad

worked also, thank you! @Sergei Baklan 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies