Thanks, that worked!
What if I need to get more granular in my analysis and plot by 2 or 4 hour
intervals, i.e., 0001-0159, 0200-0359, etc.?
"David Biddulph" wrote:
> That's an easy one, I guess. Just change Mike's 1,7,... to 0,7,...
>
> =LOOKUP(24*MOD(A1,1),{0,7,15,23},{"Night","Day","Evening","Night"})
> --
> David Biddulph
>
> "Jon M" <(E-Mail Removed)> wrote in message
> news:3B44FC10-ABF1-47BD-94FC-(E-Mail Removed)...
> > huge progress by changing from date-time to time with month, etc., and
> > time
> > commands.
> > only rub now is that anything with an hour value of "0," i.e., 0:36 gives
> > #n/a
> >
> > getting ready to head to work but if there is an easy fix for this last
> > step
> > i would appreciate it.
> >
> > thanks,
> >
> > "David Biddulph" wrote:
> >
> >> Presumably you suggested amending the suggested formula to
> >> =LOOKUP(24*A1,{1,7,15,23},{"Night","Day","Evening","Night"}) ?
> >> If so, which times gave you which wrong result?
> >>
> >> But it may get a wee bit confused with times like 14:95 :-)
> >> --
> >> David Biddulph
> >>
> >> "Jon M" <(E-Mail Removed)> wrote in message
> >> news:66C72BDE-A126-44B6-B0E4-(E-Mail Removed)...
> >> > I'm probably just slower than most but I can't get it top work.
> >> > Using Excel 2007.
> >> > Have a column with values like "5:48," etc.
> >> >
> >> > Would like to have a value like:
> >> >
> >> > 1 or days for 0700-1495
> >> > 2 or evening for 1500-2259
> >> > 3 or nights for 2300-0659
> >> >
> >> > Any further help available?
> >> >
> >> > "Mike H" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> Try this,
> >> >>
> >> >> =LOOKUP(24*A1,{1,7,15},{"Night","Day","Night"})
> >> >>
> >> >> This will give 2 shifts 07:00 - 15:00 = days
> >> >> all other time nights
> >> >> I think you should be able to work out how to do it if you want 3 or 4
> >> >> different shifts.
> >> >>
> >> >> Mike
> >> >>
> >> >> "Jon M" wrote:
> >> >>
> >> >> > Working with spreadshift containing date and times of events during
> >> >> > a
> >> >> > one
> >> >> > month period. I converted the cell content from date and time to
> >> >> > time
> >> >> > (23:00
> >> >> > format).
> >> >> >
> >> >> > Now I want to go back and associate events with a given shift, i.e.,
> >> >> > If
> >> >> > B2>=(7,0,0) AND time <=(15,0,0), "Days" (or it could just be a
> >> >> > value, ie.,
> >> >> > 1)
> >> >> >
> >> >> > If I want to assign the value to one of three shifts, can I just
> >> >> > string
> >> >> > all
> >> >> > three formulas together and separate with commas?
> >> >> >
> >> >> > Keep getting formula error.
> >> >> > Arghhh.
> >> >> >
> >> >> > Thanks in advance for any help.
> >> >> > Help?????
> >>
> >>
> >>
>
>
>
Similar Threads
