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

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

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

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

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