Forum Discussion
IF with specific date and send to user with dd/mm/yyyy format
I’m attempting to create a formula that does the following:
- Looks to see if it’s the first of the month and if so returns that month with year
- If any other day other than the first of the month returns quarter and year
I also not sure how to complete this after 2021 and so forth as well as making sure it converts to a European short date format of DD/MM/YYYY
For example:
Date Output
1/1/2021 Jan 2021
1/31/2021 Q1/2021
4/1/2021 Apr 2021
4/30/2021 Q2/2021
12/1/2021 Dec 2021
12/31/2021 Q4/2021
This is what I did:
- To solve 1/1/2021, 4/1/2021, 12/1/2021
=CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dec")&" "&YEAR(A1)
- Then to complete the formula to get any other date I could do
=IF(A1=AND(DATEVALUE("1/1/2021"),DATEVALUE("4/1/2021"),DATEVALUE("12/1/2021")),ROUNDUP(MONTH(A1)/3,0)&"/"&YEAR(A1),CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dec")&" "&YEAR(A1))
My first step works fine as it gives me my output but on all lines. My second attempts to fix that but all I get is the value of the first. I noticed when I take out the days after 1/1/2021 in the first nested AND() it works. I’m not sure what to do.
Thank you for any help!
2 Replies
- Jpalaci1Brass ContributorThank you so much! That worked.