Date Functions

B

Bonniem

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
 
B

bpeltzer

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
 
D

Dave O

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).
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
D

Dave O

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
Top