Time range to workdays in relevant months

[Lukino]

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

 

[Tom Ogilvy]

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.

 

[Lukino]

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:

 

[Tom Ogilvy]

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

 

[Lukino]

Hi,
it looks great. Thanks a lot
Lukas

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