Between Function

  • Thread starter Thread starter Nigel Graham
  • Start date Start date
N

Nigel Graham

This may sound stupid but it is a very common DB function
but I can not find the function in Excel.
I need to create an funtion something like =IF(A1 BETWEEN
00:00 AND 14:00,"Early",IF(A1 BETWEEN 14:01 AND
22:00,"Late","Night"))).
the times are shown as 24 hour clock. I need to sort out
workloads for staff and allocate jobs by their input time
associating them to a shift.
In SQL those are comon functions but appear to be missing
in Excel. Anyone help me.
 
One way:

A B C D E
1 0:00 Early 0:00 Early
2 0:01 Early 14:01 Late
3 0:02 Early 22:01 Night
4 13:58 Early
5 13:59 Early
6 14:00 Early
7 14:01 Late
8 14:02 Late
9 21:58 Late
10 21:59 Late
11 22:00 Late
12 22:01 Night
13 22:02 Night
14 22:03 Night

B1: =VLOOKUP(A1,D$1:E$3,2)
 
Thanks Mike but I have found the correct function now
through reading. Excel does not have the Between function
but a getaround is:-
the AND function.
=IF(AND(AY8>0.2499,AY8<0.5833),"Early",IF(AND
(AY8>0.5832,AY8<0.9166),"Late","Night"))
Point to note the decimal value for the time has to be
used and not the real time.
Thanks very much for the thoughts and time Mike.
Shouldn't MS add the Between function?
 
Nigel Graham said:
This may sound stupid but it is a very common DB function
but I can not find the function in Excel.
I need to create an funtion something like =IF(A1 BETWEEN
00:00 AND 14:00,"Early",IF(A1 BETWEEN 14:01 AND
22:00,"Late","Night"))).
the times are shown as 24 hour clock. I need to sort out
workloads for staff and allocate jobs by their input time
associating them to a shift.
In SQL those are comon functions but appear to be missing
in Excel. Anyone help me.
Click to expand...

To answer your question literally, you need the AND function along with two
time comparisons to get 'between', like this:
=IF(AND(A1>=TIME(0,0,0),A1<=TIME(14,0,0)),"Early",IF(AND(A1>TIME(14,0,0),A1<
=TIME(22,0,0)),"Late","Night"))

Be careful to distinguish between < and <= so that your formula does exactly
what you want at the crossover times, remembering that 14:01 is NOT one time
increment above 14:00 to Excel (which always stores times to much greater
precision whether you use/display this or not).

However, in your example, all you would need is:
=IF(A1<=TIME(14,0,0),"Early",IF(A1<=TIME(22,0,0),"Late","Night"))
Time cannot be < 0, so you don't need the first comparison.
By the time Excel gets to the second IF, it has already decided that the
time is not <= 14:00, so you don't need the > 14:00 comparison.
In other words, with these type of nested IFs, if you start with the
smallest comparison and work logically through to ever larger ones, you
never need the AND function.
 
Back
Top