Paul from Ontario asks how to create a timesheet dropdown showing all of the weeks ending on Sunday. Episode 660 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast I'm Bill Jelen.
Well It's the day after Christmas and hopefully you had a great holiday.
Maybe got some cool gifts like an iPod touch, or maybe an Xbox 360, maybe a new PC. Watch out for that new PC.
Someone may have slipped and gotten you the one with Vista and office 2007.
Now if you have Vista and office 2007 ofcourse, I want to point out that I have some great books "Excel 2007 miracles made easy" will get you up that learning curve through Excel 2007 and for the rest of the year at the MrExcel store you can use this coupon code techme for 20% off on any of our excel 2007 books, CDs, DVDs, a whole bit there.
Well hey today we have a question from Paul. Paul wrote in from southern Ontario says.
Hey I'm creating a time sheet.
I need a drop-down list with all of the week ending dates in our weeks happen to end on Sunday.
So let's take a look at a couple of different functions here.
The today function is a simple little function that will return today's date and what I want to do then, is use the weekday function =WEEKDAY of today basically. Now normally if we would just use this function, it would say that Sunday is 1 and Saturday is 7 but if we put a comma 2 at the end then we'll see that basically the number Monday is 1. So what I'm going to do is.
I'm going to create a nice little list out here to the side.
It says =TODAY()-WEEKDAY(TODAY(),2) and that will return the most recent Sunday, and then from there, I'm going to add 7 In case someone is filling out their timesheets in advance or I'll go back a few weeks, so =E4 - 7 To give us the last few weeks And this list now basically will become the source for my data validation drop-down.
So somewhere where they're going to enter the date, we use data validation.
Instead of allowing any value we want to allow a list and specify that our list is out here in E1 through E7.
Now what happens is this tiny little list will give us all of the weeks, and it will be updated so if we come in three months from now that list will have a list of the recent weeks and the current week and a few future weeks.
Basically allowing anyone to fill out any list they would ever need.
So thanks to Paul for sending in that great question and thanks to you for stopping by. See you next time for another netcast from MrExcel.
Well It's the day after Christmas and hopefully you had a great holiday.
Maybe got some cool gifts like an iPod touch, or maybe an Xbox 360, maybe a new PC. Watch out for that new PC.
Someone may have slipped and gotten you the one with Vista and office 2007.
Now if you have Vista and office 2007 ofcourse, I want to point out that I have some great books "Excel 2007 miracles made easy" will get you up that learning curve through Excel 2007 and for the rest of the year at the MrExcel store you can use this coupon code techme for 20% off on any of our excel 2007 books, CDs, DVDs, a whole bit there.
Well hey today we have a question from Paul. Paul wrote in from southern Ontario says.
Hey I'm creating a time sheet.
I need a drop-down list with all of the week ending dates in our weeks happen to end on Sunday.
So let's take a look at a couple of different functions here.
The today function is a simple little function that will return today's date and what I want to do then, is use the weekday function =WEEKDAY of today basically. Now normally if we would just use this function, it would say that Sunday is 1 and Saturday is 7 but if we put a comma 2 at the end then we'll see that basically the number Monday is 1. So what I'm going to do is.
I'm going to create a nice little list out here to the side.
It says =TODAY()-WEEKDAY(TODAY(),2) and that will return the most recent Sunday, and then from there, I'm going to add 7 In case someone is filling out their timesheets in advance or I'll go back a few weeks, so =E4 - 7 To give us the last few weeks And this list now basically will become the source for my data validation drop-down.
So somewhere where they're going to enter the date, we use data validation.
Instead of allowing any value we want to allow a list and specify that our list is out here in E1 through E7.
Now what happens is this tiny little list will give us all of the weeks, and it will be updated so if we come in three months from now that list will have a list of the recent weeks and the current week and a few future weeks.
Basically allowing anyone to fill out any list they would ever need.
So thanks to Paul for sending in that great question and thanks to you for stopping by. See you next time for another netcast from MrExcel.
