network days in formula (Excel 2003 SP3)

[steveh]

Afternoon all

I have attached a sample spreadsheet in which delays are calculated based on the departure date and time and the arrival date and time which calculates the transit based on the expected trasnit time and the actual time taken (the results are in the delay column)

I am wondering if I could add a formula into another column to recognise if the expected transit time was 48 hours and the departure day was a Thursday or a Friday 48 hours has to be added to the transit, if the transit time is 24 hours and the departure day is Friday add 48 hours.

It seems an impossible ask to me but I know that you formula guys might be able to prove me wrong.

[HansV]

The extra transit time for row 4 would be

=(WEEKDAY(E4)>=5)*(WEEKDAY(E4)<=6)*(I4=48)*48+(WEEKDAY(E4)=6)*(I4=24)*48

If you want to add the expected transit time and the extra transit time together, use

=I4+(WEEKDAY(E4)>=5)*(WEEKDAY(E4)<=6)*(I4=48)*48+(WEEKDAY(E4)=6)*(I4=24)*48

[steveh]

The extra transit time for row 4 would be

=(WEEKDAY(E4)>=5)*(WEEKDAY(E4)<=6)*(I4=48)*48+(WEEKDAY(E4)=6)*(I4=24)*48

If you want to add the expected transit time and the extra transit time together, use

=I4+(WEEKDAY(E4)>=5)*(WEEKDAY(E4)<=6)*(I4=48)*48+(WEEKDAY(E4)=6)*(I4=24)*48

Hi Hans

That is absolutely brilliant, thank you very much, I really did think that this would be impossible to do. When you have a moment could you please let me know what 14 is in the formula and what it does, for example 14=48?

Also in recognition of your MVP status your sig should surely be HansmVp now

[StuartR]

...When you have a moment could you please let me know what 14 is in the formula and what it does, for example 14=48?
...

Steve,

This is not (14=48), the first character is letter I, not digit 1, it references column I of row 4 of the Worksheet.

[HansV]

As Stuart says, it's I4 - the cell in row 4 containing the expected transit time.

[steveh]

As Stuart says, it's I4 - the cell in row 4 containing the expected transit time.

Thanks Hans and Stuart

Can somebody design a Homer smilie smacking his head and saying doh!!