Stuck in Formula Creation

[@tul]

Hi All,

I am attaching a sheet in which I try to make a formula for date change if Column A Date Time is After 7 pm or on Sunday then Change the Next Day date at morning 10 Am also if resloved date is same as creation date than dont change.

But I stuck in 3 particular dates that (12 March 2017(sunday),19 March 2017(sunday) & 26 March 2017(sunday)).

As per formula if date is sunday it will change to next date but due to ofice working on this date I want same creation date in column B but not able to do.

Highlighted with yellow colors which date have proble.

Hope I am able to explain my problem.

Pleas help me.

 

[bosco_yip]

Try,

In G2, formula copied down :

=IF((MOD(INT(A2),7)=1)*(MOD(A2,1)>19/24)*(INT(A2)<>INT(D2)),INT(A2)+1.41666666666667,A2)

Regards
Bosco

 

[@tul]

Try,

In G2, formula copied down :

=IF((MOD(INT(A2),7)=1)*(MOD(A2,1)>19/24)*(INT(A2)<>INT(D2)),INT(A2)+1.41666666666667,A2)

Regards
Bosco

Hi Bosco,

thanks for reply but in that we are not checking if creation date is on sunday or not, because if creation date is sunday we want next day on that also except pre -specified 3 dates.

please suggest if possible.

Thanks

 

[bosco_yip]

Hi Bosco,

thanks for reply but in that we are not checking if creation date is on sunday or not, because if creation date is sunday we want next day on that also except pre -specified 3 dates.

please suggest if possible.

Thanks

1] In G2, revised formula copied down :

=IF(((MOD(INT(A2),7)=1)+(MOD(A2,1)>19/24))*(INT(A2)<>INT(D2)),INT(A2)+1.41666666666667,A2)

2] Condition is :

Date is on Sunday OR Time is after 7 pm AND Resolved date <> Creation date.

Regards
Bosco