Rolling Date

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Rolling Date

Post by D Willett »

Hi. I have a series of text boxes, 6 in total for Mon, Tues, Weds, Thurs, Fri, Sat. They represent the days of this week. They are part of a work in progress board so operatives can see what is due for completion "this week".
How can I have each text box show the date for the corresponding day and roll over to next weeks day when today is complete?

Thanks
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rolling Date

Post by HansV »

Format the text boxes with the date format that you prefer, e.g. Short Date or Long Date.
Set the Control Source of the first text box to

=Date()-Weekday(Date(),2)+1

And that of the second text box to

=Date()-Weekday(Date(),2)+2

etc., up to

=Date()-Weekday(Date(),2)+6

for the sixth one.
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Rolling Date

Post by D Willett »

Hi Hans. Yes that works for this week.
If today is (example) Tuesday can next Mondays date show in Monday so we have a rolling result?

Thanks
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Rolling Date

Post by HansV »

Oh, I thought you always wanted to show Monday to Saturday of the current week.

For the first one:

=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)>1)+1

For the second one:

=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)>2)+2

etc., up to

=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)>6)+6
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Rolling Date

Post by D Willett »

By golly thats cracked it.
Thanks Hans
Regards
Cheers ...

Dave.