Formula Rqrd WITH =TODAY function

[sayavanambc]

Halo friends,
Could you please help me? I want a formula to display my shift details in cell A1.Which should be updated daily according to the date.
ie
I have 1 to 140 shift schedule details in M column M1-M140
I want my today shift to be displayed in A1 cell (M1 contents)
tomorrow it has to show next shift.
Likewise it has increase day after day automatically when the date is changing.
One more thing after shift 140 it has to return ti shift 1 instead of shift 141.
Pl help me

 

[p45cal]

=INDIRECT("M" & MOD(TODAY()-DATE(2011,4,23)+1,140))
or
=OFFSET($M$1,MOD(TODAY()-DATE(2011,4,23),140),)
?
The DATE(2011,4,23) part is a hard coded base date from which you want to stat counting - adjust to your base date.

 

[sayavanambc]

=INDIRECT("M" & MOD(TODAY()-DATE(2011,4,23)+1,140))
or
=OFFSET($M$1,MOD(TODAY()-DATE(2011,4,23),140),)


excellent!! thankssssssssss.

 

[mikerickson]

A non-volatile solution

=INDEX($M:$M, TODAY-DATE(2011,4,23)+1, 1)