How to get a date formula based on several criteria

[dude6571]

Hi all,

Based on a warranty date, I would like to

• Set a Date and Time
• Date should be at least 2 days before the warranty expiration date.
• If warranty date is a Monday, date should be at least Wednesday from previous week @ 10 am or 2 pm
• Days must only be Monday, Tuesday, Wednesday
• Time should be 10 am and 2 pm
• Date & Time Format: dd-MM-yyyy hh:mm:ss

Example:

• Warranty date Thursday 12-04-2018 -> Result: 09-04-2018 10:00:00 (Monday)
• Warranty date Thursday 12-04-2018-> Result: 09-04-2018 14:00:00 (Monday)
• Warranty date Monday 07-05-2018-> Result: 02-05-2018 10:00:00 (Wednesday)
• Warranty date Monday 07-05-2018-> Result: 02-05-2018 14:00:00 (Wednesday)

I come up with following formula but I am stuck to get a correct formula with several dates (see XL sheet attached)

 

[dude6571]

 

[dude6571]

[TABLE="width: 590"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]=TEXT(B1,"dddd")
[/TD]
[TD]=(B1-WEEKDAY(B1,1)+2)+TIME(A1,,)[/TD]
[TD]=TEXT(D1,"dddd")[/TD]
[/TR]
[TR]
[TD]Warranty Date[/TD]
[TD]Day[/TD]
[TD]Date - Time
[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]12-04-2018[/TD]
[TD]Thursday[/TD]
[TD]09-04-2018 10:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]07-05-2018
[/TD]
[TD]Monday[/TD]
[TD]02-05-2018 14:00:00[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]07-05-2018[/TD]
[TD]Monday[/TD]
[TD]02-05-2018 10:00:00[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]07-05-2018[/TD]
[TD]Monday[/TD]
[TD]03-05-2018 14:00:00[/TD]
[TD]Thursday[/TD]
[/TR]
[TR]
[TD]01-06-2018[/TD]
[TD]Friday[/TD]
[TD]28-05-2018 10:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]03-06-2018[/TD]
[TD]Sunday[/TD]
[TD]29-05-2018 14:00:00[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]03-06-2018[/TD]
[TD]Sunday[/TD]
[TD]29-05-2018 10:00:00[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]03-06-2018[/TD]
[TD]Sunday[/TD]
[TD]29-05-2018 14:00:00[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]13-06-2018[/TD]
[TD]Wednesday[/TD]
[TD]11-06-2018 10:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]16-06-2018[/TD]
[TD]Saturday[/TD]
[TD]11-06-2018 14:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]24-06-2018[/TD]
[TD]Sunday[/TD]
[TD]19-06-2018 10:00:00[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]25-06-2018[/TD]
[TD]Monday[/TD]
[TD]25-06-2018 14:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]05-07-2018[/TD]
[TD]Thursday[/TD]
[TD]02-07-2018 10:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]12-07-2018[/TD]
[TD]Thursday[/TD]
[TD]09-07-2018 14:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]19-07-2018[/TD]
[TD]Thursday[/TD]
[TD]16-07-2018 10:00:00[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]23-07-2018[/TD]
[TD]Monday[/TD]
[TD]17-07-2018 14:00:00[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]31-07-2018[/TD]
[TD]Tuesday[/TD]
[TD]25-07-2018 10:00:00[/TD]
[TD]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]