Last working day of month

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JudithJubilee - 05 Jun 2007 12:45 GMT

Hello there,

I'm using 2003. I know that you can use the fill series on dates to get
every weekday, every 4th of the month, etc. However, I would like to be able
to get a list of the dates for the last Friday of each month. Any ideas?

Thanks in advance

Signature

Hope this helps

Reply to this Message

Bob Phillips - 05 Jun 2007 13:01 GMT

=DATE(YEAR(A1),MONTH(A1)+1,0)+1-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)+2)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello there,
>
[quoted text clipped - 4 lines]
>
> Thanks in advance

Reply to this Message

Bob Phillips - 05 Jun 2007 13:03 GMT

To list all months, put this in B1

=DATE(YEAR(TODAY()),ROW(A1)+1,0)+1-WEEKDAY(DATE(YEAR(TODAY()),ROW(A1)+1,0)+2)

and copy down

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello there,
>
[quoted text clipped - 4 lines]
>
> Thanks in advance

Reply to this Message

ShaneDevenshire - 05 Jun 2007 23:55 GMT

Hi,

If you have a list of the a day of each month (any day) in A1:A12, then

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7)

If you just want to enter one day in the first month in cell A1 then use the
formula in B1:

=EOMONTH(A1,ROW()-1)-MOD(EOMONTH(A1,ROW()-1)+1,7)

If you don't want to enter the formula on row 1 then

=EOMONTH(A1,ROW(A1)-1)-MOD(EOMONTH(A1,ROW(A1)-1)+1,7)

Signature

Cheers,
Shane Devenshire

> Hello there,
>
[quoted text clipped - 3 lines]
>
> Thanks in advance

Reply to this Message

Peo Sjoblom - 05 Jun 2007 23:59 GMT

Note though that the EMONTH function is part of the ATP and some companies
do not install ATP on their employees computers

Signature

Regards,

Peo Sjoblom

> Hi,
>
[quoted text clipped - 20 lines]
>>
>> Thanks in advance

Reply to this Message

ShaneDevenshire - 06 Jun 2007 02:25 GMT

Hi,

If you enter a date from each month (any day) in A1:A12 then enter this
formula in B1:B12:

=EOMONTH(A1,0)-MOD(EOMONTH(A1,0)+1,7)

Or if you just want to enter one date in Jan in A1 use this formula in B1
and copy it down:

=EOMONTH(A1,ROW()-1)-MOD(EOMONTH(A1,ROW()-1)+1,7)

If you don't want to enter the formula on row 1 to start:

=EOMONTH(A1,ROW(A1)-1)-MOD(EOMONTH(A1,ROW(A1)-1)+1,7)

Signature

Cheers,
Shane Devenshire

> Hello there,
>
[quoted text clipped - 3 lines]
>
> Thanks in advance

Reply to this Message