Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
Hi,
I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
You are wrong! WORKDAY does take care both of weekends and holidays! Check it
again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
Exactly! I want it to include weekends, not ignore them.
I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
If you have the start date in A1, number of days in B1 and holidays dates in
column C then =A1+B1-(COUNTA(C:C)+1) returns 14/01/09 if A1=01/01/09, B1=14 and there are no holidays in column C. It returns 12/01/09 if you have two holidays in column C. Regards, Stefi €˛Jock€¯ ezt Ć*rta: Exactly! I want it to include weekends, not ignore them. I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
you need a UDF to do this
Option Explicit Function WorkingDays(startdate As Date, nDays As Long, Optional holidays As Range) As Date ' INPUT : start date ' : number of days (n) ' : range of holidays (optional) 'OUTPUT: Date n days from Start Date adjusting fir holidays Dim i As Long Dim thisDate As Date thisDate = startdate i = 1 Do Until i = nDays If Not isHoliday(thisDate, holidays) Then i = i + 1 End If thisDate = thisDate + 1 Loop WorkingDays = thisDate End Function Function isHoliday(sDate As Date, source As Range) As Boolean On Error Resume Next isHoliday = WorksheetFunction.Match(sDate * 1, source, False) < 0 End Function "Jock" wrote in message ... Exactly! I want it to include weekends, not ignore them. I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
This looks promising!
I have more than one column which would need to use this UDF so can I incorporate those or would this be specific to one range? Can you help with the input lines - ie if you give an example, I can adapt to suit? Thanks, -- Traa Dy Liooar Jock "Patrick Molloy" wrote: you need a UDF to do this Option Explicit Function WorkingDays(startdate As Date, nDays As Long, Optional holidays As Range) As Date ' INPUT : start date ' : number of days (n) ' : range of holidays (optional) 'OUTPUT: Date n days from Start Date adjusting fir holidays Dim i As Long Dim thisDate As Date thisDate = startdate i = 1 Do Until i = nDays If Not isHoliday(thisDate, holidays) Then i = i + 1 End If thisDate = thisDate + 1 Loop WorkingDays = thisDate End Function Function isHoliday(sDate As Date, source As Range) As Boolean On Error Resume Next isHoliday = WorksheetFunction.Match(sDate * 1, source, False) < 0 End Function "Jock" wrote in message ... Exactly! I want it to include weekends, not ignore them. I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
Will any of the dates for your holidays ever occur on a weekend? If **not**,
you could use NETWORKDAYS to calculate the number of holidays occurring between your start and end dates by subtracting a NETWORKDAYS function call with a reference to your holiday list from a NETWORKDAYS function call without a reference to the holiday list... and subtract that number from the difference between your start and end dates plus 1 (to include both the start and end dates in your count). For example, A1: Start Date A2: End Date H1: Start Holiday List H9: End Holiday List =A2-A1+1-(NETWORKDAYS(A1,A2)-NETWORKDAYS(A1,A2,H1:H2)) Remember, though, this assumes no holidays will take place on a weekend. -- Rick (MVP - Excel) "Jock" wrote in message ... Exactly! I want it to include weekends, not ignore them. I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORKDAY and holidays
I have a sheet called Holidays, and a table named BankHolidays
sheet1 has my dates in D5... and the days in E5.... the answer will be put in D5... Option Explicit Sub setstopdate() Dim holidays As Range Dim cell As Range Set holidays = Worksheets("Holidays").Range("BankHolidays") For Each cell In Range("D5:D25") With cell .Offset(, 2) = WorkingDays(.Value, .Offset(, 1).Value, holidays) End With Next End Sub alternative in D5: =WorkingDays(D5,E5,BankHolidays) "Jock" wrote in message ... This looks promising! I have more than one column which would need to use this UDF so can I incorporate those or would this be specific to one range? Can you help with the input lines - ie if you give an example, I can adapt to suit? Thanks, -- Traa Dy Liooar Jock "Patrick Molloy" wrote: you need a UDF to do this Option Explicit Function WorkingDays(startdate As Date, nDays As Long, Optional holidays As Range) As Date ' INPUT : start date ' : number of days (n) ' : range of holidays (optional) 'OUTPUT: Date n days from Start Date adjusting fir holidays Dim i As Long Dim thisDate As Date thisDate = startdate i = 1 Do Until i = nDays If Not isHoliday(thisDate, holidays) Then i = i + 1 End If thisDate = thisDate + 1 Loop WorkingDays = thisDate End Function Function isHoliday(sDate As Date, source As Range) As Boolean On Error Resume Next isHoliday = WorksheetFunction.Match(sDate * 1, source, False) < 0 End Function "Jock" wrote in message ... Exactly! I want it to include weekends, not ignore them. I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using WORKDAY -- Traa Dy Liooar Jock "Stefi" wrote: You are wrong! WORKDAY does take care both of weekends and holidays! Check it again! Regards, Stefi €˛Jock€¯ ezt Ć*rta: Hi, I need to have a date formula which will not ignore weekends and will take into account a list of holiday dates. WORKDAY takes care of the holiday list but ignores weekends. Is there another function that will include weekends AND holidays? p.s. any weekends within the holiday dates can be ignored. Thanks -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add no. of days to a workday to get new workday? | Excel Worksheet Functions | |||
Holidays | Excel Worksheet Functions | |||
Workday With Weekends Excluding Holidays | Excel Worksheet Functions | |||
Workday - Saturdays Included - Holidays Excluded | Excel Worksheet Functions | |||
=SUM((B7+2),IF($D7>0.Workday,($D7+E$8,Holidays!$C4:$C11$),'''')) | Excel Worksheet Functions |