| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time range to workdays in relevant months
Hello,
I really need to fill relevant workdays to cells represents relevant months from only StartDay and FinishDay. I know (example): StartDay FinishDay 4.5.2006 11.7.2006 I need (example): April May June July August 0 20 22 7 0 PLS, help me Thanks a lot Lukas |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time range to workdays in relevant months
You can use the NetWorkdays function from the analysis toolpak - Demo'd from
the immediate window: ? application.Run("ATPVBAEN.XLa!NetWorkdays" ,DateValue("May 4, 2006"),DateValue("May 31, 2006")) 20 Or just use the formula on your worksheet. -- Regards, Tom Ogilvy "Lukino" wrote: Hello, I really need to fill relevant workdays to cells represents relevant months from only StartDay and FinishDay. I know (example): StartDay FinishDay 4.5.2006 11.7.2006 I need (example): April May June July August 0 20 22 7 0 PLS, help me Thanks a lot Lukas |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time range to workdays in relevant months
using Networkdays function isn't problem. My problem is presentation of
results to simple calendar. As I wrote on exaple "I need" PLS, do you have some ideas? Thanks Tom Ogilvy pÃ*Å¡e: You can use the NetWorkdays function from the analysis toolpak - Demo'd from the immediate window: ? application.Run("ATPVBAEN.XLa!NetWorkdays" ,DateValue("May 4, 2006"),DateValue("May 31, 2006")) 20 Or just use the formula on your worksheet. -- Regards, Tom Ogilvy "Lukino" wrote: Hello, I really need to fill relevant workdays to cells represents relevant months from only StartDay and FinishDay. I know (example): StartDay FinishDay 4.5.2006 11.7.2006 I need (example): April May June July August 0 20 22 7 0 PLS, help me Thanks a lot Lukas |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time range to workdays in relevant months
maybe something like this:
Sub abc() Dim dt1 As Date, dt2 As Date Dim dta1 As Date, dta2 As Date Dim dtb1 As Date, dtb2 As Date Dim dtc1 As Date, dtc2 As Date Dim a As Long dt1 = DateSerial(2006, 5, 4) dt2 = DateSerial(2006, 7, 11) dta1 = DateSerial(Year(dt1), Month(dt1), 1) dta2 = DateSerial(Year(dt2), Month(dt2), Day(DateSerial(Year(dt2), Month(dt2) + 1, 0))) diff = DateDiff("m", dta1, dta2) For i = 0 To diff dtb1 = DateSerial(Year(dta1), Month(dta1) + i, 1) dtb2 = DateSerial(Year(dtb1), Month(dtb1), Day( _ DateSerial(Year(dtb1), Month(dtb1) + 1, 0))) dtc1 = Application.Max(dtb1, dt1) dtc2 = Application.Min(dtb2, dt2) a = Application.Run("ATPVBAEN.XLa!NetWorkdays", dtc1, _ dtc2) Cells(1, i + 1).Value = Format(dtc1, "mmm") Cells(2, i + 1).Value = a Next i End Sub -- Regards, Tom Ogilvy "Lukino" wrote: using Networkdays function isn't problem. My problem is presentation of results to simple calendar. As I wrote on exaple "I need" PLS, do you have some ideas? Thanks Tom Ogilvy pÃ*Å¡e: You can use the NetWorkdays function from the analysis toolpak - Demo'd from the immediate window: ? application.Run("ATPVBAEN.XLa!NetWorkdays" ,DateValue("May 4, 2006"),DateValue("May 31, 2006")) 20 Or just use the formula on your worksheet. -- Regards, Tom Ogilvy "Lukino" wrote: Hello, I really need to fill relevant workdays to cells represents relevant months from only StartDay and FinishDay. I know (example): StartDay FinishDay 4.5.2006 11.7.2006 I need (example): April May June July August 0 20 22 7 0 PLS, help me Thanks a lot Lukas |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Time range to workdays in relevant months
Hi,
it looks great. Thanks a lot Lukas "Tom Ogilvy" wrote: maybe something like this: Sub abc() Dim dt1 As Date, dt2 As Date Dim dta1 As Date, dta2 As Date Dim dtb1 As Date, dtb2 As Date Dim dtc1 As Date, dtc2 As Date Dim a As Long dt1 = DateSerial(2006, 5, 4) dt2 = DateSerial(2006, 7, 11) dta1 = DateSerial(Year(dt1), Month(dt1), 1) dta2 = DateSerial(Year(dt2), Month(dt2), Day(DateSerial(Year(dt2), Month(dt2) + 1, 0))) diff = DateDiff("m", dta1, dta2) For i = 0 To diff dtb1 = DateSerial(Year(dta1), Month(dta1) + i, 1) dtb2 = DateSerial(Year(dtb1), Month(dtb1), Day( _ DateSerial(Year(dtb1), Month(dtb1) + 1, 0))) dtc1 = Application.Max(dtb1, dt1) dtc2 = Application.Min(dtb2, dt2) a = Application.Run("ATPVBAEN.XLa!NetWorkdays", dtc1, _ dtc2) Cells(1, i + 1).Value = Format(dtc1, "mmm") Cells(2, i + 1).Value = a Next i End Sub -- Regards, Tom Ogilvy "Lukino" wrote: using Networkdays function isn't problem. My problem is presentation of results to simple calendar. As I wrote on exaple "I need" PLS, do you have some ideas? Thanks Tom Ogilvy pÃ*Å¡e: You can use the NetWorkdays function from the analysis toolpak - Demo'd from the immediate window: ? application.Run("ATPVBAEN.XLa!NetWorkdays" ,DateValue("May 4, 2006"),DateValue("May 31, 2006")) 20 Or just use the formula on your worksheet. -- Regards, Tom Ogilvy "Lukino" wrote: Hello, I really need to fill relevant workdays to cells represents relevant months from only StartDay and FinishDay. I know (example): StartDay FinishDay 4.5.2006 11.7.2006 I need (example): April May June July August 0 20 22 7 0 PLS, help me Thanks a lot Lukas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference in needed and delivered time with workdays only | Excel Worksheet Functions | |||
| Difference between Date & Time for workdays | Excel Worksheet Functions | |||
| date add 5 workdays AND 6 calendar months | Excel Worksheet Functions | |||
| Calculate Total hours worked during workdays within given shift time. | Excel Discussion (Misc queries) | |||
| How do I find total number workdays from a range of date | Excel Worksheet Functions | |||
Linear Mode
