Remember Me?

#1
December 23rd 08, 01:30 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2008 Posts: 26
Last working day of a month

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#2
December 23rd 08, 01:40 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,276
Last working day of a month

Hi,

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#3
December 23rd 08, 01:42 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
Last working day of a month

Hi,

I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of
the month but try this

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

or if you have the analysis toolpak addin the simpler

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

Mike

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#4
December 23rd 08, 01:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
Last working day of a month

=WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DAT E(YEAR(A1),MONTH(A1)+1,1)-1)-1,NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)))

Will return the last working day of the month of date you enter in A1
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#5
December 23rd 08, 01:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
Last working day of a month

Aplolgies for my elderly moment I gave you a formula for the last Friday of a
minth. For the last working day use

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

Mike

"Mike H" wrote:

Hi,

I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of
the month but try this

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

or if you have the analysis toolpak addin the simpler

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

Mike

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#6
December 23rd 08, 01:53 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2008 Posts: 26
Last working day of a month

Jamal

"Eduardo" wrote:

Hi,

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#7
December 23rd 08, 01:53 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
Last working day of a month

Note that with my suggestion, you'll need to go to Tools-AddIns and activate
the Analysis ToolPak.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#8
December 23rd 08, 01:56 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,276
Last working day of a month

Hi Jamal,

"Jamal" wrote:

Jamal

"Eduardo" wrote:

Hi,

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

#9
December 23rd 08, 02:27 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
Last working day of a month

On Tue, 23 Dec 2008 05:30:01 -0800, Jamal
wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

It appears from your example, that you want the last working day of the month
in the month AFTER the date in A1.

That being the case:

=WORKDAY(EOMONTH(A1,1)+1,-1)

If you want the last working day of the SAME month as in A1, then:

=WORKDAY(EOMONTH(A1,0)+1,-1)

Note that there is an optional Holidays argument for the Workday function, if
you want to take that into account also.

If you receive the #NAME! error, then look at HELP for the WORKDAY function --
it will tell you how to install the Analysis ToolPak.
--ron
#10
December 24th 08, 12:12 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 53
Last working day of a month

Hi Jamal,

=IF(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7)<2,(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-1,7))-1,DATE(YEAR(A1),MONTH(A1)+1,1)-1)

It'll work in all versions of Excel. even without addins

--

Kind Regards,
Satti Charvak
Only an Excel Enthusiast

"Jamal" wrote:

I am trying to write a formula to show the last working day (working days are
Monday to Friday) of a month based on the date in the previous cell. If A1 =
1/4/08, A2 should show 30/5/08 (Friday). I have tried
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+30). However, this picks up the day in
A1+30. What I want is the last working day of the month not the last day of
the month. I appreciate any help with this formula. Thanks in advance.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Matt Excel Worksheet Functions 14 November 13th 08 03:52 PM Hedgie Excel Worksheet Functions 2 January 8th 08 03:35 PM JudithJubilee New Users to Excel 5 June 6th 07 02:25 AM PG Excel Discussion (Misc queries) 4 January 23rd 07 08:01 PM Winston New Users to Excel 9 November 3rd 05 04:49 PM

All times are GMT +1. The time now is 01:18 PM.