Any Nested Function & Condition For Calculating Date & Time
 

Start Time = 09:30
End Time = 16:00

Senario A

A3 = 01-JAN 08:28
B3 = 01-JAN 10:54

Q (1) If (A3 < 09:30, 09:30, 08:28)

Q (2) C3 = (B3 - A3),where if (A3 < 09:30, 09:30, 08:28)

Q (3) If (B3 16:00, 16:00, 10:54)

Senario B

A3 = 01-JAN 16:28
B3 = 02-JAN 08:34

Q(1)
If A3 (date) < B3 (date), 02-JAN 09:30 (X)
If B3 (date) = A3 (date), B3
B3 - A3 = C3
If B3 (time) < 09:30, 02-JAN 09:30
If B3 (time) 09:30, B3

Any solution for this?

Any Nested Function & Condition For Calculating Date & Time
 

Here is something to start with
=IF(TIME(HOUR(A3),MINUTE(A3),0)<TIME(9,30,0),"A"," B")

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Safu" wrote in message
...
Start Time = 09:30
End Time = 16:00

Senario A

A3 = 01-JAN 08:28
B3 = 01-JAN 10:54

Q (1) If (A3 < 09:30, 09:30, 08:28)

Q (2) C3 = (B3 - A3),where if (A3 < 09:30, 09:30, 08:28)

Q (3) If (B3 16:00, 16:00, 10:54)

Senario B

A3 = 01-JAN 16:28
B3 = 02-JAN 08:34

Q(1)
If A3 (date) < B3 (date), 02-JAN 09:30 (X)
If B3 (date) = A3 (date), B3
B3 - A3 = C3
If B3 (time) < 09:30, 02-JAN 09:30
If B3 (time) 09:30, B3

Any solution for this?

Any Nested Function & Condition For Calculating Date & Time
 

TQ Bernard & I've create the formula as follow for TIME:-

=IF(TIME(HOUR(A3),MINUTE(A3),0)<TIME(9,30,0),TIME( 9,30,0),A3)

Next further question is there any formula to retain the SAME value of date
if time is < 09:30. Where the value should given as 01-JAN 09:30.

"Bernard Liengme" wrote:

Here is something to start with
=IF(TIME(HOUR(A3),MINUTE(A3),0)<TIME(9,30,0),"A"," B")

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Safu" wrote in message
...
Start Time = 09:30
End Time = 16:00

Senario A

A3 = 01-JAN 08:28
B3 = 01-JAN 10:54

Q (1) If (A3 < 09:30, 09:30, 08:28)

Q (2) C3 = (B3 - A3),where if (A3 < 09:30, 09:30, 08:28)

Q (3) If (B3 16:00, 16:00, 10:54)

Senario B

A3 = 01-JAN 16:28
B3 = 02-JAN 08:34

Q(1)
If A3 (date) < B3 (date), 02-JAN 09:30 (X)
If B3 (date) = A3 (date), B3
B3 - A3 = C3
If B3 (time) < 09:30, 02-JAN 09:30
If B3 (time) 09:30, B3

Any solution for this?

Any Nested Function & Condition For Calculating Date & Time
 

Not sure I follow
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Safu" wrote in message
...
TQ Bernard & I've create the formula as follow for TIME:-

=IF(TIME(HOUR(A3),MINUTE(A3),0)<TIME(9,30,0),TIME( 9,30,0),A3)

Next further question is there any formula to retain the SAME value of
date
if time is < 09:30. Where the value should given as 01-JAN 09:30.

"Bernard Liengme" wrote:

Here is something to start with
=IF(TIME(HOUR(A3),MINUTE(A3),0)<TIME(9,30,0),"A"," B")

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Safu" wrote in message
...
Start Time = 09:30
End Time = 16:00

Senario A

A3 = 01-JAN 08:28
B3 = 01-JAN 10:54

Q (1) If (A3 < 09:30, 09:30, 08:28)

Q (2) C3 = (B3 - A3),where if (A3 < 09:30, 09:30, 08:28)

Q (3) If (B3 16:00, 16:00, 10:54)

Senario B

A3 = 01-JAN 16:28
B3 = 02-JAN 08:34

Q(1)
If A3 (date) < B3 (date), 02-JAN 09:30 (X)
If B3 (date) = A3 (date), B3
B3 - A3 = C3
If B3 (time) < 09:30, 02-JAN 09:30
If B3 (time) 09:30, B3

Any solution for this?