Hi all,
I need to write a fairly complex formula in Excel to do with time and dates.
In Column A I will have the 'Type': either Red or Blue.
In Column B, the 'Date purchase date and time.'
And Column C is the 'Due date and time.' Column C is where the formula needs to be.
Red Type.
A "Red" type means it is due in 1 hour. So if the time is 10am, the time needs to be set to 11am in the due date and time column. However if the purchase time is after 3.01pm, the due time needs to be set to 9am the next day. I.e 3pm will equate to 4pm, however 3.01pm will mean it is due the next day at 9am.
THis is where it gets tricky.... if the date is on the Friday after 3pm, Saturday all day, or Sunday all day, the due date needs to be set to the monday after at 9am.
Blue Type.
A "Blue" type means it is due in 24 hours. So the time and date need to be set to 24 hours later. If it is received on a Friday it should be set to the same time on the Monday.
SO a normal week day i.e Tuesday 11am will be due on Wednesday at 11am.
So 2pm on the Friday received, equates to being due on the Monday at 2pm. Saturday 2pm, equates to Monday 2pm, Sunday 2pm equates to Monday 2pm.
I have attached an Excel spreadsheet with samples of how the results should look.
Thanks in advance!
Paul
Bookmarks