trying to use a range of times and then input specific text

[robaston1971]

excel help.PNG

Hi,

I hope someone can help with a formula for what I am trying to do as I am pretty useless with Excel!

I am trying to put in a specific value into the box on the end that is marked as red from the box with text underneath it.

I want to use a range of times EG if the time is between 23:30 and 01:29 in the box that shows the time then I want it to populate the other box with specific text (2330-0129), however, I need this to cover an entire day which will give me 12 ranges:

2330-0129
0130-0329
0330-0529
0530-0729
0730-0929
0929-1129
1130-1329
1330-1529
1530-1729
1730-1929
1930-2129
2130-2329

So to try and un-complicate, if the time in the first marked box is between any of those times then it should show the text I have pasted in the end box.

If anyone has any idea I would be very grateful.

Cheers

[robaston1971]

adding a sample

[JeteMc]

This solution makes use of three helper columns. Column V has the range of times from post #1. Column W has the following formula to isolate the decimal portion of a day represented by the left hand side of the range in column V:

Please Login or Register  to view this content.

Column X has the following formula to isolate the decimal portion of a day represented by the right hand side of the range in column V:

Please Login or Register  to view this content.

Column S can then be populated by applying the following formula:

Please Login or Register  to view this content.

Here is a renamed copy of your file with the helper columns and formulas applied:
Time ranges.xlsx
Let me know if you have any questions.

[robaston1971]

Awesome thanks , will give it a go

[robaston1971]

just checked it with a load more data and its working like a dream, thanks a lot, really appreciated

[JohnTopley]

Version without helper columns;

=INDEX($V$2:$V$14,MATCH($C2,$U$2:$U$14,1))

Table in columns U & V

Not exhaustively tested!

[JeteMc]

just checked it with a load more data and its working like a dream, thanks a lot, really appreciated

You're welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a nice day.