IF with specific date and send to user with dd/mm/yyyy format

%3CLINGO-SUB%20id%3D%22lingo-sub-2120111%22%20slang%3D%22en-US%22%3EIF%20with%20specific%20date%20and%20send%20to%20user%20with%20dd%2Fmm%2Fyyyy%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%E2%80%99m%20attempting%20to%20create%20a%20formula%20that%20does%20the%20following%3A%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ELooks%20to%20see%20if%20it%E2%80%99s%20the%20first%20of%20the%20month%20and%20if%20so%20returns%20that%20month%20with%20year%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20any%20other%20day%20other%20than%20the%20first%20of%20the%20month%20returns%20quarter%20and%20year%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20also%20not%20sure%20how%20to%20complete%20this%20after%202021%20and%20so%20forth%20as%20well%20as%20making%20sure%20it%20converts%20to%20a%20European%20short%20date%20format%20of%20DD%2FMM%2FYYYY%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20example%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDate%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BOutput%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E1%2F1%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BJan%202021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E1%2F31%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BQ1%2F2021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E4%2F1%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BApr%202021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E4%2F30%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BQ2%2F2021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E12%2F1%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDec%202021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E12%2F31%2F2021%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BQ4%2F2021%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20is%20what%20I%20did%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ETo%20solve%201%2F1%2F2021%2C%204%2F1%2F2021%2C%2012%2F1%2F2021%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3E%3DCHOOSE(MONTH(A1)%2C%22Jan%22%2C%22Feb%22%2C%22Mar%22%2C%22Apr%22%2C%22May%22%2C%22Jun%22%2C%22Jul%22%2C%22Aug%22%2C%22Sept%22%2C%22Oct%22%2C%22Nov%22%2C%22Dec%22)%26amp%3B%22%20%22%26amp%3BYEAR(A1)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3EThen%20to%20complete%20the%20formula%20to%20get%20any%20other%20date%20I%20could%20do%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3E%3DIF(A1%3DAND(DATEVALUE(%221%2F1%2F2021%22)%2CDATEVALUE(%224%2F1%2F2021%22)%2CDATEVALUE(%2212%2F1%2F2021%22))%2CROUNDUP(MONTH(A1)%2F3%2C0)%26amp%3B%22%2F%22%26amp%3BYEAR(A1)%2CCHOOSE(MONTH(A1)%2C%22Jan%22%2C%22Feb%22%2C%22Mar%22%2C%22Apr%22%2C%22May%22%2C%22Jun%22%2C%22Jul%22%2C%22Aug%22%2C%22Sept%22%2C%22Oct%22%2C%22Nov%22%2C%22Dec%22)%26amp%3B%22%20%22%26amp%3BYEAR(A1))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20first%20step%20works%20fine%20as%20it%20gives%20me%20my%20output%20but%20on%20all%20lines.%20My%20second%20attempts%20to%20fix%20that%20but%20all%20I%20get%20is%20the%20value%20of%20the%20first.%20I%20noticed%20when%20I%20take%20out%20the%20days%20after%201%2F1%2F2021%20in%20the%20first%20nested%20AND()%20it%20works.%20I%E2%80%99m%20not%20sure%20what%20to%20do.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%26nbsp%3Byou%20for%20any%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2120111%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120137%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20with%20specific%20date%20and%20send%20to%20user%20with%20dd%2Fmm%2Fyyyy%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(DAY(A1)%3D1%2CTEXT(A1%2C%22mmm%20yyyy%22)%2C%22Q%22%26amp%3BQUOTIENT(MONTH(A1)%2B2%2C3)%26amp%3B%22%2F%22%26amp%3BYEAR(A1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2145616%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20with%20specific%20date%20and%20send%20to%20user%20with%20dd%2Fmm%2Fyyyy%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2145616%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%20That%20worked.%3C%2FLINGO-BODY%3E
Contributor

I’m attempting to create a formula that does the following:

  1. Looks to see if it’s the first of the month and if so returns that month with year
  2. 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:

 

  1. 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)

 

  1. 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

@Jpalaci1 

Try this:

 

=IF(DAY(A1)=1,TEXT(A1,"mmm yyyy"),"Q"&QUOTIENT(MONTH(A1)+2,3)&"/"&YEAR(A1))

Thank you so much! That worked.