Marking a cell based on times within a certain period

[nathp]

HI All

Hoping someone is able to help.

I have a column showing a date and time formula (pulled directly from an SQL report). The format is displayed as dd/mm/yyyy hh:mm. I want to add a comment in an adjacent cell based on any times that fall within the hours 01:00 to 05:00. This is irrespective of the date.

Honestly I have no idea how to even start so any help would be much appreciated.

Many thanks

EDIT: I should add that the cell I am basing the comment on is a calculation formula based on another cell.

So the original cell for instance shows: 29/05/2023 13:05. Then a calculation is added to another cell referencing this cell as follows: =C3+0.16666666666 showing the result 29/05/2023 17:05 (this is a time zone calculation). It is this last cell that will be used to provide the comment in the adjacent cell based on the time conditions mentioned above.

 

[pjmorris]

use this formula:

=IF(AND(MOD(A1,1)>1/24,MOD(A1,1)<=5/24), "this is after 0100 and before or equal to 0500","")

It works because time is the decimal part of a date/time number. Dates are the integer part.

HTH

 

[nathp]

Thanks for the quick reply but I couldnt get that to work sorry. I replaced A1 with C6 as per my sheet but stated a missing parenthesis?
Also what will the result be in the new cell?

Thanks

 

[pjmorris]

If it says you've a missing parenthesis it means you've not copied the formula across correctly or deleted a bracket when changing A1 to C6. You need to carefully compare your formula with that given above. If the time is after 0100 and before or equal to 0500 the response will say "this is after 0100 and before or equal to 0500"; if you need something different then simply amend the formula accordingly.

 

[nathp]

Not sure what I'm missing but I think its copied correctly. It give me the same message even before editing the cell ref.

 

[pjmorris]

you've only put in half the formula which is why its missing a parenthesis. you need the complete formula, not just the bit up to the first comma which is what you're showing above:

=IF(AND(MOD(C6,1)>1/24,MOD(C6,1)<=5/24), "this is after 0100 and before or equal to 0500","")

 

[nathp]

Sorry - I thought that was just an explanation.

Thanks for the persistence and help.

 

[pjmorris]

no problem. If you don't know, you don't know! Hopefully this has been a bit helpful - don't be afraid to play with the formula to see what you can do with it.

If you're happy with the solution please mark it as such.

Regards

 

[nathp]

HI Peter.

One last issue.

Having implemented the formula I am getting one result showing as positive even though it does not fall between the specified times. I tried changing the 5/24 to 8/24 or 2/24 to test but still it shows a positive result.

The formula has been copied in to all of column L in the following screenshot.

(Bet your glad you replied now)

 

[nathp]

Ignore it I have seen the mistake - I was referencing the wrong cell.

Thanks for your help, Solution marked as such