Forumla dates

[ghynes]

hello..

im looking for formula which will give me an output related to the date.

heres what i am looking for.
i have a list of work shifts in column A, and start date in column B and end date in column C. i need an output on a diff cell to tell me the shift thats working on the present day.

SHIFTC 3/27/2007 7:00 3/27/2007 19:00
SHIFTD 3/27/2007 19:00 3/28/2007 7:00
SHIFTC 3/28/2007 7:00 3/28/2007 19:00
SHIFTB 3/28/2007 19:00 3/29/2007 7:00
SHIFTA 3/29/2007 7:00 3/29/2007 19:00
SHIFTB 3/29/2007 19:00 3/30/2007 7:00
SHIFTA 3/30/2007 7:00 3/30/2007 19:00
SHIFTD 3/30/2007 19:00 3/31/2007 7:00

a diff cell would output SHIFTC as today is 3/27/2007 12:08 and then would change to SHIFTD when the time would change to 3/27/2007 19:00.

can you help?

[BigBas]

you can input this formula into your output cell:

=INDEX(A1:C8,MATCH(TODAY(),B1:B4,0),1)

Let me know if that works for you.

[ghynes]

thanks for the reply. i tried this but it didnt work.
i managed to come up with this formula

=IF(AND(NOW() >= C2,NOW() <= D2),B2, "")

and this has worked. the only thing is though i have 730 rows because i have 1/1/2007 7:00:00 AM to 1/1/2007 19:00 for every day of the year and the shifts in column A!
i have the above formula the next column over so that every day 12hours it will give a new shift. the word shift X (X being whatever shift) will appear where the formula is. how do i get this to appear in just one cell on another sheet? every day the value will be one cell further down. is there a formula that will give me the data from the one cell??