Choose a specific calculating formula for a cell

[jpsabourin]

How do I get a cell to look at another cell and when a specific
parameter is met it chooses the correct formula

i.e.

First parameters

A1: cell has the value 07/04/2007 9:45 Available at
B1: cell has the value 07/04/2007 17:00 Due by Time
C1: cell has the value 07/04/2007 14.30 Time delivered

Calculation A for this parameter would be =c1-a1

Second parameters

A1: cell has the value 07/04/2007 12:30 Available at
B1: cell has the value 08/04/2007 12:00 Due by Time
C1: cell has the value 08/04/2007 10.30 Time delivered

Calculation B for this parameter would be =c1-a1-15/24

HOW DO I GET CELL D1 to choose calculation A when the due by time is
17:00 or calculation B when the due by time is 12:00

Thanks
JP

 

[Roger Govier]

Hi

=IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,""))

The final null "" is for the situation if B1 is neither 12:00 nor 17:00.
Amend to suit.

 

[jpsabourin]

Hi

=IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,""))

The final null "" is for the situation if B1 is neither 12:00 nor 17:00.
Amend to suit.

--
Regards

Roger Govier













- Show quoted text -

You are good, thank you very much for your help

 

[Roger Govier]

You're very welcome. Thanks for the feedback

 

[jpsabourin]

You're very welcome. Thanks for the feedback

--
Regards

Roger Govier







- Show quoted text -

Just one more tiny favour, if the hour is not defined by 12 or 17 can
a formula still be achieved.

I see you given me the none ""> >> =IF(HOUR(B1)=12,C1-
A1-15/24,IF(HOUR(B1)=17,C1-A1,""))

Let say I have orders that do not need this, example a difference
service level

 

[Roger Govier]

Hi

The formula I gave would insert a null in the cell, if the value in B1
was neither 17:00 nor 12:00.
With your latest example, 14:30 would come into either category, hence
there would not be a calculation showing in the C column.

Is there some other situation or some other calculation that should
prevail if the time is 14:30?
Can you give a list of the conditions, and what time calculation you
want to show up?

 

[jpsabourin]

Hi

The formula I gave would insert a null in the cell, if the value in B1
was neither 17:00 nor 12:00.
With your latest example, 14:30 would come into either category, hence
there would not be a calculation showing in the C column.

Is there some other situation or some other calculation that should
prevail if the time is 14:30?
Can you give a list of the conditions, and what time calculation you
want to show up?

--
Regards

Roger Govier









- Show quoted text -

I have figured out the rest of formula required, by removing the none,
it works fine.