| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date and Time Function | Excel Discussion (Misc queries) | |||
| How to get date entered recording first time condition is true? | Excel Discussion (Misc queries) | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| Help - Information with time and date | Excel Discussion (Misc queries) | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
