The attached workbook requires users to enter a date on the SAT sheet (B3). I would like that date to always represent Saturday (the first day of our work week) Is there a formula I can use to validate if the date entered by the user corrleates to Saturday or is there a way to auto-populate the workbook with the Saturday's date?
The following formula will return the most recent Saturday each time the workbook is opened:
=TODAY()-MOD(WEEKDAY(TODAY()),7)
If it is Saturday, it will return today's date (not next or last week's Saturday).
That just gives me a FALSE. I want it to automatically fill that field with Saturday's date.
If it gives you FALSE, you've done something wrong. I downloaded your workbook and put that formula in B3 and it works exactly as requested. I created a new workbook and it works exactly as requested.
Can you attach yours please? Its still coming up wrong for me. Thanks by the way!
and just to confirm, when you entered that formula, the cell was populated with 12/07/2013 and all of the same cells on subsequent pages were 12/8, 12/9, etc?
Here's a sample file with the formula in cell A1. The formula in B1 just shows that value as the text day of week. Column D shows dates between 12/1 and 12/21/2013, and column E has the corresponding previous Saturday value for each using a similar formula (replacing the TODAY() function with the cell reference from column D).
E1: =D1-MOD(WEEKDAY(D1),7)
E2: =D2-MOD(WEEKDAY(D2),7)
etc.
On your Sun, Mon, etc. tabs, if you wanted to show the date for that Sunday, Monday, etc. you could just add 1 to the previous tab's B3 value.
On SUN tab: =SAT!B3+1
On MON tab: =SUN!B3+1
On TUE tab: =MON!B3+1
etc.
tapsmiled.xlsx
So, whenever someone opens this for the first time, it will populate the date of the previous Saturday? Is there a way that, if it were opened on a Friday night (prior to midnight) it would populate Saturday (tomorrow)? i used the A1 formula from your sheet for this.
I'm trying to dummy-proof the form since it has the potential to be used by many people. I can't guarantee that they will create a new workbook on Saturday.....some might start next week's book on Friday. I hope I am making sense.
Last edited by tapsmiled; 12-10-2013 at 09:01 PM.
I tried, unsuccesfully, to write an IF statement using the formula you provided. I'm not really familiar with the MOD function, so it didn't work. I tried to have it check if TODAY was <=5 (Thursday, right?) and if so, it would use your original formula. The Value If false is where I had a problem.
You can probably use something along the lines of:
=IF(WEEKDAY(TODAY())>=5,TODAY()-WEEKDAY(TODAY())+7,TODAY()-MOD(WEEKDAY(TODAY()),7))
I will try that. Thanks so much for the assistance.
....but that formula will also give you the following Saturday on Thursday as well as Friday - try this version
=TODAY()+2-WEEKDAY(TODAY()+2)
Today is Wednesday so currently that will give the previous Saturday....and tomorrow it will give the same date but on Friday it will change to the following Saturday
To test replace TODAY() in the formula with a cell reference, e.g.
=A1+2-WEEKDAY(A1+2)
and try various dates in A1
Audere est facere
Woops, good catch. Should have used >=6 instead in mine. Yours seems a lot simpler, DDL (as usual).
Okay, so if the user creates a new workbook, depending upon the day of creation, it will populate that cell with either the past Saturday's date or the following Saturday's date, right?
What you were doing is similar to what I attempted, but your version was much better than mine. You guys are awesome!Originally Posted by Paul
Woops, good catch. Should have used >=6 instead in mine. Yours seems a lot simpler, DDL (as usual).
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks