Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work Week, Excel 2000 & 2003
Hello,
Is there a way to modify Chip Person work day function so that it will list First Workday of week and last work day of week. For example: Jan 7 - Jan 11 2008 Jan 14 - Jan 18 2008 Jan 21 - Jan 25 2008 Jan 28 - Feb 1 2008 Feb 4 - Feb 8 2008 =IF(OR(WEEKDAY(C4+1)=1,WEEKDAY(C4+1)=7),C4+3,C4+1) Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work Week, Excel 2000 & 2003
Hello,
I found a macro that would list First Workday of week and last work day of week as noted above example: Sub FirstLastWorkWeekDay() 'First & Last work day of week 'http://groups.google.com/group/ microsoft.public.excel.worksheet.functions/browse_thread/thread/ 19fd3df90c368977/b05b3ca186ccb301?hl=en&lnk=gst&q=work +week#b05b3ca186ccb301 'Put the year into cell A1, then run the macro. _ Not sure what you wanted to do if your week ended up being one day - _ (Jan1 on a Friday, or Dec 31 on a Monday) so I did Jan1-Jan1 and _ Dec31-Dec31 for those cases. 'Set Variables Dim myDate As Date Dim myYear As Integer Dim StartDate As Date Dim EndDate As Date Dim WeekString As String Dim dayString As String 'Start Date begin 1/1 StartDate = DateValue("1/1/" & Range("A1").Value) 'End Date end 12/31 EndDate = DateValue("12/31/" & Range("A1").Value) ' For myDate = StartDate To EndDate dayString = Format(myDate, "ddd") If dayString < "Sat" And dayString < "Sun" Then If WeekString = "" Then WeekString = Format(myDate, "mmmm d") & " - " End If If dayString = "Fri" Then WeekString = WeekString & Format(myDate, "mmmm d") Range("A500").End(xlUp)(2).Value = WeekString WeekString = "" End If End If Next myDate If WeekString < "" Then WeekString = WeekString & Format(EndDate, "mmmm d") Range("A500").End(xlUp)(2).Value = WeekString End If End Sub Thanks, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros created in 2003 won't work in 2000 | Excel Discussion (Misc queries) | |||
Macros won't work in 2000 that are created in 2003 | Excel Discussion (Misc queries) | |||
Calculations done in Excel 2000 don't work in Excel 2003 | Excel Discussion (Misc queries) | |||
Hyperlinks from Excel 2000 dont work anymore in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2000 to 2003 update links doesn't work properly any suggest | Excel Worksheet Functions |