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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Number of working days
Hi,
I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#2
|
|||
|
|||
Number of working days
Hi,
I would try to compute the number of week changes with a week starting the same day as the first date, multiply by 5, then, assuming the last date is also a week date, add its day of the week number, asw if the week still start as the first day. From #02-07-2007# to #02-23-2007#: ? DatePart("w", #02-07-2007#) 4 ? DateDiff("ww", #02-07-2007#, #02-23-2007#, 4) ' note the third argument 2 ? DatePart("w", #02-23-2007#, 4) ' again, note the third argument 3 ? 2*5+3 13 as in the 13 works day. You have to remove non-working days that are special holidays on a weekday. You probably have put them in a table, then, you COUNT the number of records, in that table, that have a date between your two dates, to get the number of special holidays. That was assuming the working days were Monday to Friday, included. Probably irrelevant though, but it is clearly untested for other cases. If the last day is not a working day, you have to correct the formula accordingly. Same if the first day is not a working day. Hoping it may help, Vanderghast, Access MVP wrote in message oups.com... Hi, I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#3
|
|||
|
|||
Number of working days
I usually have a table of holidays (tbl_Holidays) that I update each year,
that contains Hol_Date and Hol_Name fields. I also have a table of numbers (tbl_Numbers) with a int_Value field and data ranging from 0 to 999. To find out the number of working days between two two dates, I run a query that looks something like the following. What this query does is start out by creating a nested subquery that identifies all of the dates (SomeDate) between a [Start_Date] and an [End_Date]. It then joins this subquery to my holidays table with a left join (to get all of the dates). Finally, I sum on the value (1) for each of the days where the datepart("w", SomeDate) is between Monday (2) and Friday (6) where the day is not in the holidays table. PARAMETERS [Start_Date] DateTime, [End_Date] DateTime; SELECT Sum(1) AS WorkDays FROM (SELECT DateAdd("d",[Start_Date],[int_Value]) AS SomeDate FROM tbl_Numbers WHERE DateAdd("d",[Start_Date],[int_Value]) Between [Start_Date] And [End_Date]) AS InclusiveDates LEFT JOIN tbl_Holidays ON InclusiveDates.SomeDate = tbl_Holidays.Hol_Date WHERE DatePart("w",[SomeDate]) Between 2 And 6 AND tbl_Holidays.Hol_Date Is Null; HTH Dale -- Email address is not valid. Please reply to newsgroup only. " wrote: Hi, I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#4
|
|||
|
|||
Number of working days
.... forgot my previous solution, it does not work well, as, by
counter-example, from #02-07-2007# to #02-20-2007#. Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Hi, I would try to compute the number of week changes with a week starting the same day as the first date, multiply by 5, then, assuming the last date is also a week date, add its day of the week number, asw if the week still start as the first day. From #02-07-2007# to #02-23-2007#: ? DatePart("w", #02-07-2007#) 4 ? DateDiff("ww", #02-07-2007#, #02-23-2007#, 4) ' note the third argument 2 ? DatePart("w", #02-23-2007#, 4) ' again, note the third argument 3 ? 2*5+3 13 as in the 13 works day. You have to remove non-working days that are special holidays on a weekday. You probably have put them in a table, then, you COUNT the number of records, in that table, that have a date between your two dates, to get the number of special holidays. That was assuming the working days were Monday to Friday, included. Probably irrelevant though, but it is clearly untested for other cases. If the last day is not a working day, you have to correct the formula accordingly. Same if the first day is not a working day. Hoping it may help, Vanderghast, Access MVP wrote in message oups.com... Hi, I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#5
|
|||
|
|||
Number of working days
Maybe a working solution, based on DateDiff("ww",...) would be: 7-DatePart("w", firstDate) + DatePart("w", lastDate)-1 + 5*DateDiff("ww", firstDate, 7) firstDate=#02-07-2007#: lastDate=#02-23-2007#: ? (7-DatePart("w", firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate, lastDate, 7) 18 firstDate=#02-07-2007#: lastDate=#02-20-2007#: ? (7-DatePart("w", firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate, lastDate, 7) 15 That still assumes that firstDate and lastDate are weekdays, and that Saturday and Sunday are not workdays. Hoping it may help, Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... ... forgot my previous solution, it does not work well, as, by counter-example, from #02-07-2007# to #02-20-2007#. Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Hi, I would try to compute the number of week changes with a week starting the same day as the first date, multiply by 5, then, assuming the last date is also a week date, add its day of the week number, asw if the week still start as the first day. From #02-07-2007# to #02-23-2007#: ? DatePart("w", #02-07-2007#) 4 ? DateDiff("ww", #02-07-2007#, #02-23-2007#, 4) ' note the third argument 2 ? DatePart("w", #02-23-2007#, 4) ' again, note the third argument 3 ? 2*5+3 13 as in the 13 works day. You have to remove non-working days that are special holidays on a weekday. You probably have put them in a table, then, you COUNT the number of records, in that table, that have a date between your two dates, to get the number of special holidays. That was assuming the working days were Monday to Friday, included. Probably irrelevant though, but it is clearly untested for other cases. If the last day is not a working day, you have to correct the formula accordingly. Same if the first day is not a working day. Hoping it may help, Vanderghast, Access MVP wrote in message oups.com... Hi, I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#6
|
|||
|
|||
Number of working days
Thank you very much,
I will try. Much appreciated of your help. Michel Walsh wrote: Maybe a working solution, based on DateDiff("ww",...) would be: 7-DatePart("w", firstDate) + DatePart("w", lastDate)-1 + 5*DateDiff("ww", firstDate, 7) firstDate=#02-07-2007#: lastDate=#02-23-2007#: ? (7-DatePart("w", firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate, lastDate, 7) 18 firstDate=#02-07-2007#: lastDate=#02-20-2007#: ? (7-DatePart("w", firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate, lastDate, 7) 15 That still assumes that firstDate and lastDate are weekdays, and that Saturday and Sunday are not workdays. Hoping it may help, Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... ... forgot my previous solution, it does not work well, as, by counter-example, from #02-07-2007# to #02-20-2007#. Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Hi, I would try to compute the number of week changes with a week starting the same day as the first date, multiply by 5, then, assuming the last date is also a week date, add its day of the week number, asw if the week still start as the first day. From #02-07-2007# to #02-23-2007#: ? DatePart("w", #02-07-2007#) 4 ? DateDiff("ww", #02-07-2007#, #02-23-2007#, 4) ' note the third argument 2 ? DatePart("w", #02-23-2007#, 4) ' again, note the third argument 3 ? 2*5+3 13 as in the 13 works day. You have to remove non-working days that are special holidays on a weekday. You probably have put them in a table, then, you COUNT the number of records, in that table, that have a date between your two dates, to get the number of special holidays. That was assuming the working days were Monday to Friday, included. Probably irrelevant though, but it is clearly untested for other cases. If the last day is not a working day, you have to correct the formula accordingly. Same if the first day is not a working day. Hoping it may help, Vanderghast, Access MVP wrote in message oups.com... Hi, I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? Can you help me how to use the codes to calculate working days plus the leap year days? Your help would be much appreciated. Thanks |
#7
|
|||
|
|||
Number of working days
On Feb 23, 6:58 am, "
wrote: I use the codes to calculate the working days but how do I know if it will calculate the days including leap year. Is there a way to know how many working days of the leap year withing the date range from jan 1 2000 to jan 1 2010 ? See: Why should I consider using an auxiliary calendar table? http://www.aspfaq.com/show.asp?id=2519 "A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions: How many business days between x and y? ... As you can imagine, all of these answers require complex iterative code that steps through and applies logic to each of the dates in a range. Or do they...? " Jamie. -- |
Thread Tools | |
Display Modes | |
|
|