If formula with Time

[rubz71]

Hi Guys,

Need your help with an 'if' formula with TIME.

If clock-in time is between 9.00pm and 11:59pm (cell A1), and clock-out time is higher than 12:00am (cell B1), then update the time to 12:00am (cell c1), or leave it as it is. Thanks!

you help is highly appreciated.

Thanks

[NeedForExcel]

Try the attached file!

Just be careful with Time Formats.

Hope it helps!

Deep

[rubz71]

Hi mxexcelathome,

Your formula, works like magic. Thanks a lot.
However, how did you define 0.875 = 21:00? Should I wish to change that to 22:00, how do i do that. Appreciate your help on this. Thanks!

[rubz71]

I've worked it out.. Thanks though

[NeedForExcel]

Glad it helped!

Deep

[bebo021999]

If clock-in time is between 9.00pm and 11:59pm (cell A1), and clock-out time is higher than 12:00am (cell B1), then update the time to 12:00am (cell c1), or leave it as it is.

The condition for B1 has confused me! Any hour must be greater than 12:00 AM. Maybe it is unuseful.

[rubz71]

Hi Deep,

Just realise B1 Cell value is unaccounted for on your solution you provided.

If clock-in time is between 9.00pm and 11:59pm (cell A1), and clock-out time is higher than 12:00am (cell B1), then update the time to 12:00am (cell c1), or leave it as it is.

How can we incorporate B1 cell value within the formula?

[rubz71]

Just realised it. Any idea how we can incorporate B1 in the formula?

[alvin-chung]

If clock-in time is between 9.00pm and 11:59pm (cell A1), and clock-out time is higher than 12:00am (cell B1), then update the time to 12:00am (cell c1), or leave it as it is.

Is there already an existing value in cell C1?
If not, what do you mean by "leave it as it is"?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[rubz71]

C1 is blank. Leave it as it is means show the current value of A1; should the criteria (value of A1 and B1) doesn't meet.

[rubz71]

if the value of A1 is in between 9pm and 1159pm and value of B1 is higher than 12 am, c1 should show midnight (12am), or the original value of A1.

[alvin-chung]

Hi rubz71, is this what you're looking for?

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[rubz71]

Hi Alvin,

That is correct. However, the formula is not picking up B1 Value.

A1=21:30pm, B1 =23:50pm and your formula returning midnight 00:00. However, it should have returned 21:30 as B1 value is less than 00:00

[alvin-chung]

Hi rubz71,

On second thought, your criteria for B1 doesn't make sense for me as any normal time value definitely higher than 12:00 AM.

Unless, your cells in A1 and B1 are in date/time format (i.e. not only time) in which your criteria will be something like "if the value of A1 is in between today 9pm and today 1159pm and value of B1 is higher than today 12 am, c1 should show today midnight (12am), or the original value of A1"?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[rubz71]

Hi Alvin,

I understand. That make sense. However, should the formula read A1 value higher than 9pm and B1 value less than 11:59pm, display 00:00, or else display A1. Can this be doable?

[alvin-chung]

Hi rubz71,

It's doable, but the result will only display A1 if A1 <= 9pm and B1 >= 11:59pm (i.e. 11:59:00pm to 11:59:59pm).
Are you sure this is what you're looking for?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin