I am looking for a formula that will return "1st", "2nd" or "3rd" depending
on the start time of a shift. I am almost there but since there is no Else I
am having a hard time programming it with just And and If functions.

e.g. 7:25 returns 1st
15:42 returns 2nd
23:45 returns 3rd.

Thanks

The FALSE part of an IF function acts as the "else" in a programmer's If
statement. If you would tell us the range cut-offs, I'm sure someone here
would show you the formula to use.

--
Rick (MVP - Excel)


"Dan" wrote in message
...
I am looking for a formula that will return "1st", "2nd" or "3rd" depending
on the start time of a shift. I am almost there but since there is no
Else I
am having a hard time programming it with just And and If functions.

e.g. 7:25 returns 1st
15:42 returns 2nd
23:45 returns 3rd.

Thanks

With time in Col A the below formula will return the smallest

=SMALL(A:A,1)
'format it to time

=SMALL(A:A,2)
will return the 2nd smallest

If this post helps click Yes
---------------
Jacob Skaria


"Dan" wrote:

I am looking for a formula that will return "1st", "2nd" or "3rd" depending
on the start time of a shift. I am almost there but since there is no Else I
am having a hard time programming it with just And and If functions.

e.g. 7:25 returns 1st
15:42 returns 2nd
23:45 returns 3rd.

Thanks

you could create your own UDF - User Defined Function.
Maybe something like this:

Function GetShiftName(dtime As Date) As String
Select Case dtime
Case Is <= TimeValue("05:00:00")
GetShiftName = "1st"
Case Is <= TimeValue("12:00:00")
GetShiftName = "2nd"
Case Is <= TimeValue("17:00")
GetShiftName = "pm"
Case Is <= TimeValue("24:00")
GetShiftName = "night"
Case Else
GetShiftName = "u/s"
End Select
End Function



"Dan" wrote in message
...
I am looking for a formula that will return "1st", "2nd" or "3rd"
depending
on the start time of a shift. I am almost there but since there is no
Else I
am having a hard time programming it with just And and If functions.

e.g. 7:25 returns 1st
15:42 returns 2nd
23:45 returns 3rd.

Thanks