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

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

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

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

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