A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Last working day of a month



 
 
Thread Tools Display Modes
  #1  
Old December 23rd 08, 02:30 PM posted to microsoft.public.excel.misc
Jamal
external usenet poster
 
Posts: 26
Default 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.
Ads
  #2  
Old December 23rd 08, 02:40 PM posted to microsoft.public.excel.misc
Eduardo
external usenet poster
 
Posts: 2,276
Default Last working day of a month

Hi,
Take a look at C Pearson Webpage there are your answers

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

If this was helpful please say yes, thanks

"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  
Old December 23rd 08, 02:42 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default 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  
Old December 23rd 08, 02:51 PM posted to microsoft.public.excel.misc
Luke M
external usenet poster
 
Posts: 2,722
Default 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  
Old December 23rd 08, 02:51 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default 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  
Old December 23rd 08, 02:53 PM posted to microsoft.public.excel.misc
Jamal
external usenet poster
 
Posts: 26
Default Last working day of a month

Thanks. This is very helpful.

Jamal

"Eduardo" wrote:

> Hi,
> Take a look at C Pearson Webpage there are your answers
>
> http://www.cpearson.com/excel/DateTi...tm#LastWeekday
>
> If this was helpful please say yes, thanks
>
> "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  
Old December 23rd 08, 02:53 PM posted to microsoft.public.excel.misc
Luke M
external usenet poster
 
Posts: 2,722
Default 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  
Old December 23rd 08, 02:56 PM posted to microsoft.public.excel.misc
Eduardo
external usenet poster
 
Posts: 2,276
Default Last working day of a month

Hi Jamal,
I am glad it was helpful, please could you say yes, thank you

"Jamal" wrote:

> Thanks. This is very helpful.
>
> Jamal
>
> "Eduardo" wrote:
>
> > Hi,
> > Take a look at C Pearson Webpage there are your answers
> >
> > http://www.cpearson.com/excel/DateTi...tm#LastWeekday
> >
> > If this was helpful please say yes, thanks
> >
> > "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  
Old December 23rd 08, 03:27 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default 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  
Old December 24th 08, 01:12 PM posted to microsoft.public.excel.misc
Satti Charvak[_2_]
external usenet poster
 
Posts: 53
Default Last working day of a month

Hi Jamal,
Please note my formula:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Working out a value within a certain month. Matt Excel Worksheet Functions 14 November 13th 08 04:52 PM
First working day of the month Hedgie Excel Worksheet Functions 2 January 8th 08 04:35 PM
Last working day of month JudithJubilee New Users to Excel 5 June 6th 07 02:25 AM
=month formula no working PG Excel Discussion (Misc queries) 4 January 23rd 07 09:01 PM
Last Working Day In Month Winston New Users to Excel 9 November 3rd 05 05:49 PM


All times are GMT +1. The time now is 12:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.