|
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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 |
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. |
generate all fridays and last day of month
Hello,
I am trying to generate a list of dates that will give me all the fridays in a month and the last day of the month if it is not a friday. Is there any way to do this. I have been searching around and I can't find the answer. Please help if you can. For example, taking the month of Feb-10 I would like to get 2/5 Friday 2/12 Friday 2/19 Friday 2/26 Friday 2/28 last day of month Thanks, Zten microsof wrote: Hi Jamal, Please note my 24-Dec-08 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: Previous Posts In This Thread: On Tuesday, December 23, 2008 8:30 AM Jama wrote: 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. On Tuesday, December 23, 2008 8:40 AM Eduard wrote: 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: On Tuesday, December 23, 2008 8:42 AM Mike wrote: Hi,I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of 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: On Tuesday, December 23, 2008 8:51 AM Luke wrote: =WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DAT E(YEAR(A1),MONTH(A1)+1,1)-1)- =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: On Tuesday, December 23, 2008 8:51 AM Mike wrote: Aplolgies for my elderly moment I gave you a formula for the last Friday of a 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: On Tuesday, December 23, 2008 8:53 AM Jama wrote: Last working day of a month Thanks. This is very helpful. Jamal "Eduardo" wrote: On Tuesday, December 23, 2008 8:53 AM Luke wrote: Note that with my suggestion, you'll need to go to Tools-AddIns and activate Note that with my suggestion, you will 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: On Tuesday, December 23, 2008 8:56 AM Eduard wrote: Hi Jamal,I am glad it was helpful, please could you say yes, thank you"Jamal" Hi Jamal, I am glad it was helpful, please could you say yes, thank you "Jamal" wrote: On Tuesday, December 23, 2008 9:27 AM Ron Rosenfeld wrote: Last working day of a month On Tue, 23 Dec 2008 05:30:01 -0800, Jamal wrote: 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 On Wednesday, December 24, 2008 7:12 AM microsof wrote: Hi Jamal, Please note my 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: Submitted via EggHeadCafe - Software Developer Portal of Choice VIsual Studio.NET 2005 ClickOnce Technology: An Overview http://www.eggheadcafe.com/tutorials...t-2005-cl.aspx |
generate all fridays and last day of month
With Feb 5, 2010 in A2, use this in a3:
=if(a2=eomonth(a2,0),a1+7,min(a2+7,eomonth(a2,0))) and copy down Regards, Fred "Z Ten" wrote in message ... Hello, I am trying to generate a list of dates that will give me all the fridays in a month and the last day of the month if it is not a friday. Is there any way to do this. I have been searching around and I can't find the answer. Please help if you can. For example, taking the month of Feb-10 I would like to get 2/5 Friday 2/12 Friday 2/19 Friday 2/26 Friday 2/28 last day of month Thanks, Zten microsof wrote: Hi Jamal, Please note my 24-Dec-08 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: Previous Posts In This Thread: On Tuesday, December 23, 2008 8:30 AM Jama wrote: 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. On Tuesday, December 23, 2008 8:40 AM Eduard wrote: 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: On Tuesday, December 23, 2008 8:42 AM Mike wrote: Hi,I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of 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: On Tuesday, December 23, 2008 8:51 AM Luke wrote: =WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DAT E(YEAR(A1),MONTH(A1)+1,1)-1)- =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: On Tuesday, December 23, 2008 8:51 AM Mike wrote: Aplolgies for my elderly moment I gave you a formula for the last Friday of a 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: On Tuesday, December 23, 2008 8:53 AM Jama wrote: Last working day of a month Thanks. This is very helpful. Jamal "Eduardo" wrote: On Tuesday, December 23, 2008 8:53 AM Luke wrote: Note that with my suggestion, you'll need to go to Tools-AddIns and activate Note that with my suggestion, you will 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: On Tuesday, December 23, 2008 8:56 AM Eduard wrote: Hi Jamal,I am glad it was helpful, please could you say yes, thank you"Jamal" Hi Jamal, I am glad it was helpful, please could you say yes, thank you "Jamal" wrote: On Tuesday, December 23, 2008 9:27 AM Ron Rosenfeld wrote: Last working day of a month On Tue, 23 Dec 2008 05:30:01 -0800, Jamal wrote: 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 On Wednesday, December 24, 2008 7:12 AM microsof wrote: Hi Jamal, Please note my 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: Submitted via EggHeadCafe - Software Developer Portal of Choice VIsual Studio.NET 2005 ClickOnce Technology: An Overview http://www.eggheadcafe.com/tutorials...t-2005-cl.aspx |
generate all fridays and last day of month
Enter the starting date in cell A3. This need not be a Friday but it
can be. Then, in A4, enter =A3+CHOOSE(WEEKDAY($A$3),5,4,3,2,1,0,6) Then, in A5, enter =IF(A4=DATE(YEAR(A4),MONTH(A4)+1,0),"",IF(MONTH(A4 )=MONTH(A4+7),A4+7,DATE(YEAR(A4),MONTH(A4)+1,0))) This will give you a list of all Fridays in the month of the date in A3, and then the last day of the month in of month in A3, followed by empty cells. Copy this formula down as far as you need to go. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 05 Jan 2010 07:30:05 -0800, Z Ten wrote: Hello, I am trying to generate a list of dates that will give me all the fridays in a month and the last day of the month if it is not a friday. Is there any way to do this. I have been searching around and I can't find the answer. Please help if you can. For example, taking the month of Feb-10 I would like to get 2/5 Friday 2/12 Friday 2/19 Friday 2/26 Friday 2/28 last day of month Thanks, Zten microsof wrote: Hi Jamal, Please note my 24-Dec-08 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: Previous Posts In This Thread: On Tuesday, December 23, 2008 8:30 AM Jama wrote: 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. On Tuesday, December 23, 2008 8:40 AM Eduard wrote: Last working day of a month Hi, Take a look at C Pearson Webpage there are your answers http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekday If this was helpful please say yes, thanks "Jamal" wrote: On Tuesday, December 23, 2008 8:42 AM Mike wrote: Hi,I don't understand how you get 30/5/2008 from 1/4/2008 for the last day of 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: On Tuesday, December 23, 2008 8:51 AM Luke wrote: =WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DA TE(YEAR(A1),MONTH(A1)+1,1)-1)- =WORKDAY(A1,IF(WEEKDAY(A1,2)<=5,NETWORKDAYS(A1,DA TE(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: On Tuesday, December 23, 2008 8:51 AM Mike wrote: Aplolgies for my elderly moment I gave you a formula for the last Friday of a 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: On Tuesday, December 23, 2008 8:53 AM Jama wrote: Last working day of a month Thanks. This is very helpful. Jamal "Eduardo" wrote: On Tuesday, December 23, 2008 8:53 AM Luke wrote: Note that with my suggestion, you'll need to go to Tools-AddIns and activate Note that with my suggestion, you will 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: On Tuesday, December 23, 2008 8:56 AM Eduard wrote: Hi Jamal,I am glad it was helpful, please could you say yes, thank you"Jamal" Hi Jamal, I am glad it was helpful, please could you say yes, thank you "Jamal" wrote: On Tuesday, December 23, 2008 9:27 AM Ron Rosenfeld wrote: Last working day of a month On Tue, 23 Dec 2008 05:30:01 -0800, Jamal wrote: 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 On Wednesday, December 24, 2008 7:12 AM microsof wrote: Hi Jamal, Please note my 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 |
| All times are GMT +1. The time now is 03:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com