Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date field with many dates (format DD/MM/YYYY), i would like to create a new column with these conditions:
if date is between 13/08/YYYY and 17/08/YYYY -> date become 15/09/YYYY
if date is between 13/12/YYYY and 17/12/YYYY -> date become 15/01/YYYY
how can I do this?
You need to convert this into date. Try below expression
Date(If(num(Month([Your Date Field]))=8 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]),09,15),
If(num(Month([Your Date Field]))=12 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]+1),01,15),
[Your Date Field]))) as NewDate
Ohh Yes change below code.
Makedate(Year([Your Date Field]+1),01,15)
to
Makedate(Year([Your Date Field])+1,01,15),
Try this,
If(num(Month([Your Date Field]))=8 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]),09,15),
If(num(Month([Your Date Field]))=12 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]+1),01,15),
[Your Date Field])) as NewDate
Hi, it doesn't work (dont look at the strange years)
OLD DATE NEW DATE
this could be simplified as below
if(day(DATEFIELD)>=13 and day(DATEFIELD)<=17,date(monthstart(DATEFIELD),1)+14)) as newDatefield
replace DATEFIELD with your actual datefield name
You need to convert this into date. Try below expression
Date(If(num(Month([Your Date Field]))=8 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]),09,15),
If(num(Month([Your Date Field]))=12 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]+1),01,15),
[Your Date Field]))) as NewDate
Now it works, but if we are in the december case it doesn't add a Year
Ohh Yes change below code.
Makedate(Year([Your Date Field]+1),01,15)
to
Makedate(Year([Your Date Field])+1,01,15),
solved, but it was
Date(If(num(Month([Your Date Field]))=8 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year([Your Date Field]),09,15),
If(num(Month([Your Date Field]))=12 and Day([Your Date Field]) >=13 and Day([Your Date Field]) <=17,
Makedate(Year(([Your Date Field])+1),01,15),
[Your Date Field]))) as NewDate
thank you so so much!!
My Pleasure 🙂