Modify a date

[ValeriaBonini]

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?

[kaushiknsolanki]

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

[ValeriaBonini]

Hi, it doesn't work (dont look at the strange years)

OLD DATE                        NEW DATE

[vinieme12]

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

[kaushiknsolanki · Accepted solution]

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

[ValeriaBonini]

Now it works, but if we are in the december case it doesn't add a Year

[kaushiknsolanki · Accepted solution]

Ohh Yes change below code.

Makedate(Year([Your Date Field]+1),01,15) 

to

Makedate(Year([Your Date Field])+1,01,15),

[ValeriaBonini]

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

[ValeriaBonini]

thank you so so much!!

[kaushiknsolanki]

My Pleasure 🙂