Thread: adding days/hours automatically to date time using a macro

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

DonkeyOte,

thanks so much!!! seems to be working perfectly cheers!!!

Hi DonkeyOte,

The result is working perfectly but I am trying to better understand the formula you gave me...

How does it add 24 hours if it is not Red???

Once again, it is doing exactly what it is meant to do but I can't work out how you got it to add 1 day from that formula.... (I am using the second formula as I do not have the analysis pack)

I can't find that part in the formula and its driving me crazy!

Thanks a lot,

Dexter

Originally Posted by DonkeyOte

Paul,

based solely on the examples provided...

C2:
=IF(AND($A2="Red",MOD($B2,1)<="15:00"+0,NETWORKDAYS($B2,$B2,$E$1:$E$10)=1),$B2+"1:00",WORKDAY($$B2,1,$E$1:$E$10)+IF($A2="Red","9:00",MOD($B2,1)))
copied down to C13

where E1:E10 would contain public holiday dates

the above replicates your expected results.

Note use of NETWORKDAYS & WORKDAY functions necessitates activation of the Analysis ToolPak (aka ATP) Add-In (Tools -> Add-Ins).

If you are doing a lot of date based calculations I would strongly advise installing this as it will simplify your calculations.

You can achieve the same without ATP, however, should you wish/need to incorporate Public Holidays into the above then things become a little convoluted.
If you don't need to worry about Public Holidays then you can avoid ATP functions quite simply:

C2:
=IF(AND($A2="Red",MOD($B2,1)<="15:00"+0,WEEKDAY($B2,2)<6),$B2+"1:00",INT($B2)+LOOKUP(WEEKDAY($B2),{1,6,7},{1,3,2})+IF($A2="Red","9:00",MOD($B2,1)))
copied down to C17

Sorry ignore my post, I worked it out!!! The array, if weekday add 1, if Fri or Sat add 3 and 2....

Very clever,

Cheers!!!