Formula to get a schedule date and time based on a warranty date

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
Unique date and time (Incorrect Highlighted in yellow)
Time should be 10 am and 2 pm (not sure how to automate this one)
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)

See attached workbook

- formulas below pictue

.
Warranty.jpg
.

C2 - with custom format dd/mm/yyyy hh:mm
=B2+(A2/24)

E2
=IF(OR(WEEKDAY(C2)=4,WEEKDAY(C2)=5,WEEKDAY(C2)=6),C2-2,C2-WEEKDAY(C2)-3) - see correction in next post

F2 - with custom format dddd
=TEXT(E2,"DDDD")

G2 - with custom format hh:mm
=E2

Explanation of date and time
A date is an integer eg today, 10 March 2018 = 43169
A time is part of a day so noon = 12hours divided by 24 hours in day =12/24 = 0.5
0.5 is noon on any day
But if date and time are combined it becomes specific to one paricular day
43169 + 0.5 = 43169.5 = 10 March 2018 12:00


EDIT
Looking at the picture, I can see that the formula sometimes gives the wrong Wednesday - formula updated in post#3

The correct formula in E2

=IF(OR(WEEKDAY(C2)=4,WEEKDAY(C2)=5,WEEKDAY(C2)=6),C2-2,IF(WEEKDAY(C2)=7,C2-WEEKDAY(C2)+4,C2-WEEKDAY(C2)-3))

This is necessary because to Excel Saturday is day 7 and Sunday is day 1
(it is confusing because most of us think that Monday is the 1st day of the week!)

Warranty.jpg

Do you want each date and time combo to appear once only?

Is column H what you want?
Formula H2 copy down

Formula:

Please Login or Register  to view this content.

.
Warranty once only.jpg

.

If not please repost my workbook with expected results in column J
thanks

Everything matches your picture
.

EverythingMatchesUp.jpg

.

Formula in E2 copied down
=C2-IF(OR(WEEKDAY(C2)=4,WEEKDAY(C2)=5,WEEKDAY(C2)=6),2,IF(WEEKDAY(C2)=3,6,5))

Conditional formatting to make cell value invisible (it is there, you simply cannot see it)
condition is:
=COUNTIF($E$2:$E2,$E2)<>1
and font colour set the same as background

.

Conditional Formatting with countif.jpg

.
example workbook attached

I hope post#9 gave you the results you wanted

Here is a more succinct formula in E2 copied down
=C2-CHOOSE(WEEKDAY(C2),5,5,6,2,2,2,5)

Explanation
CHOOSE is multiple IF
IF WEEKDAY(C2) = 1 or 2 or 3 .....

WEEKDAY(C2) can be any value from 1 to 7

1 subtract 5
2 subtract 5
3 subtract 6
4 subtract 2
5 subtract 2
6 subtract 2
7 subtract 5

Your formula works very well up to 6 devices with same warranty dates.

which formula?
- is this conditional formatting?
- VBA will not be required (one of the ranges needs changing to match the size of your real data)

oops, please allow me to rephrase, this is conditionnal formatting.

one of the ranges needs changing to match the size of your real data

How do I do that, please?

The easiest way is to apply conditional formatting to whole columns
- then it does not matter how may items there are

Select the relevant columns and then select conditional formatting

The formula in conditional formatting needs to be generated to work for the top-left cell of any range that it is applied to
- so I amended my original formula to start at row 1 instead of row 2
=COUNTIF($E$1:$E1,$E1)<>1 (where column E is the column containing date & time values)
.

cf whole column.jpg

Glad to help.
Thanks for marking thread SOLVED and for rep