How to get a date formula based on several criteria

dude6571

New Member
Joined
Dec 2, 2016
Messages
6
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):confused::confused:
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
 
Upvote 0
[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]
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,476
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top