IF function

[mtate]

I have a column called HOUR that contains numbers from 0 to 23. I am trying
to create a formula that with populate another column called called SHIFT
with the following:

Shift should = D if HOUR is 7 thru 18
Shift should = N if HOUR is 0 thru 6 or 20, 21, 22, 23

I was using the IF function. I know this should be simple, but can't get it
to work for all.

[Niek Otten]

So, what did you try?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"mtate" <mtate@discussions.microsoft.com> wrote in message
news:6FB8F3D6-AD72-4416-9FB5-5A5A42425F77@microsoft.com...
>I have a column called HOUR that contains numbers from 0 to 23. I am
>trying
> to create a formula that with populate another column called called SHIFT
> with the following:
>
> Shift should = D if HOUR is 7 thru 18
> Shift should = N if HOUR is 0 thru 6 or 20, 21, 22, 23
>
> I was using the IF function. I know this should be simple, but can't get
> it
> to work for all.

[mtate]

=IF(J43>=7,"D",IF(J43>20,"D","N"))
Everything shows up as D, even values over 20 and from 0 to 6.

"Niek Otten" wrote:

> So, what did you try?
>
> --
> Kind regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
> "mtate" <mtate@discussions.microsoft.com> wrote in message
> news:6FB8F3D6-AD72-4416-9FB5-5A5A42425F77@microsoft.com...
> >I have a column called HOUR that contains numbers from 0 to 23. I am
> >trying
> > to create a formula that with populate another column called called SHIFT
> > with the following:
> >
> > Shift should = D if HOUR is 7 thru 18
> > Shift should = N if HOUR is 0 thru 6 or 20, 21, 22, 23
> >
> > I was using the IF function. I know this should be simple, but can't get
> > it
> > to work for all.
>
>
>

[Sandy Mann]

> > > Shift should = D if HOUR is 7 thru 18
> > > Shift should = N if HOUR is 0 thru 6 or 20, 21, 22, 23


You don't say what you want if Hour is 19 so assuming that you want Shift to
be N then try:

=IF(MOD(J43*7,133)>48,"D","N")

HTH

Sandy

[Sandy Mann]

or just:

=IF(MOD(J43,19)>6,"D","N")

--
HTH

Sandy
sandymann@mailinator.com


Replace@mailinator with @tiscali.co.uk
"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:eC7NIW3dFHA.1404@TK2MSFTNGP09.phx.gbl...
> > > > Shift should = D if HOUR is 7 thru 18
> > > > Shift should = N if HOUR is 0 thru 6 or 20, 21, 22, 23
>
>
> You don't say what you want if Hour is 19 so assuming that you want Shift
to
> be N then try:
>
> =IF(MOD(J43*7,133)>48,"D","N")
>
> HTH
>
> Sandy
>
>
>
>
>