Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
I had previously asked this question "is there a way to have excel figure out
cetain dates for example the last saturday of every month for the next year?" Answer: If you put this formula in A1, and copy/drag down, it will give you the date of the last Saturday in every month starting with Jan 2006: =DATE(2006,ROW()+1,1)-WEEKDAY(DATE(2006,ROW()+1,1)) The portion ROW()+1 denotes the month, and will change automatically as you drag the date down. So if you start, for example, in A5, you would change that to ROW()-4 Question 2: Can you tell me how to use this formula by not dragging the date down and by just using the systems time and date? What I was trying to do is set up a time table starting with the date of December 24, 2005 and carrying through using the second from last Saturday of each month. I got the formula to work by dragging but I now want to know if you can help me with the formula by using just the systems date and time? Thanks Bonnie Millward |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
The final Saturday of the current month is calculated as:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)) HTH. --Bruce "Bonniem" wrote: I had previously asked this question "is there a way to have excel figure out cetain dates for example the last saturday of every month for the next year?" Answer: If you put this formula in A1, and copy/drag down, it will give you the date of the last Saturday in every month starting with Jan 2006: =DATE(2006,ROW()+1,1)-WEEKDAY(DATE(2006,ROW()+1,1)) The portion ROW()+1 denotes the month, and will change automatically as you drag the date down. So if you start, for example, in A5, you would change that to ROW()-4 Question 2: Can you tell me how to use this formula by not dragging the date down and by just using the systems time and date? What I was trying to do is set up a time table starting with the date of December 24, 2005 and carrying through using the second from last Saturday of each month. I got the formula to work by dragging but I now want to know if you can help me with the formula by using just the systems date and time? Thanks Bonnie Millward |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
Nicely done, Bruce. The only caveat for Bonnie (the OP) is to make
sure the day numbering scheme is the Excel default(Sunday = 1, Saturday = 7). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
On Fri, 2 Dec 2005 05:45:02 -0800, "Bonniem"
wrote: I had previously asked this question "is there a way to have excel figure out cetain dates for example the last saturday of every month for the next year?" Answer: If you put this formula in A1, and copy/drag down, it will give you the date of the last Saturday in every month starting with Jan 2006: =DATE(2006,ROW()+1,1)-WEEKDAY(DATE(2006,ROW()+1,1)) The portion ROW()+1 denotes the month, and will change automatically as you drag the date down. So if you start, for example, in A5, you would change that to ROW()-4 Question 2: Can you tell me how to use this formula by not dragging the date down and by just using the systems time and date? What I was trying to do is set up a time table starting with the date of December 24, 2005 and carrying through using the second from last Saturday of each month. I got the formula to work by dragging but I now want to know if you can help me with the formula by using just the systems date and time? I don't understand why you want to start with 24 DEC 2005 since that is not the last Saturday in December??? But to get the last Saturday of "this month", use the formula: =TODAY()+40-DAY(TODAY()+39)-WEEKDAY(TODAY()+40-DAY(TODAY()+39)) If the formula is in A1, you can use the following formula to get the last Saturday of the subsequent month: =A1+40-DAY(A1+39)-WEEKDAY(A1+40-DAY(A1+39)) --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
Another way to skin this cat: I worked on it too long to not post!
This assumes the Excel default day numbering, where Sunday = 1 and Saturday = 7. Derive the last Saturday in the month with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-7) Yes, the (7-7) yields zero in this context- but you can find the last Friday with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-6) and the last Thursday with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-5) etc. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Functions
On 2 Dec 2005 08:16:31 -0800, "Dave O" wrote:
Another way to skin this cat: I worked on it too long to not post! This assumes the Excel default day numbering, where Sunday = 1 and Saturday = 7. Derive the last Saturday in the month with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-7) Yes, the (7-7) yields zero in this context- but you can find the last Friday with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-6) and the last Thursday with =EOMONTH(TODAY(),0)-MOD(WEEKDAY(EOMONTH(TODAY(),0)),7)-(7-5) etc. Of course, your formula requires the installation of the Analysis ToolPak. Not all places allow or want that. Also, MOD(Weekday,... is redundant as Weekday already implies a MOD ...7 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Date Functions | Excel Worksheet Functions | |||
Date functions | New Users to Excel | |||
Date Math Problem | Excel Worksheet Functions | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |