Hi,

I have a sheet with collums for entry date and exit date used in a car-
compound. I also have a collumn for each month of the year.

What I am trying to do is let excel (VBA) calculate how many days a
car was on the compound in every month. This is a monthly/yearly
ocurring item to check the invoicing.

What I'd like to do is to pull data from our database into the excel
sheet, just the chassis number, entry date and exit date. I would
like to paste this in my excel, and then be presented with the amount
of days a car was on the compound in each month.

Is there any way to this ? I know that if it was a full month, it's
easy to do, but let's say it has an entry on 13.01.06 and an exit on
20.10.06 , then it becomes more complicated. Also the calculation has
to be performed on several thousand cars at once...

Any help ??

Bart,

This solution assumes the following:

Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting
in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D,
you have the dates for the first of the month, for the time period that you are interested in: Aug
1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like...

Then in cell D3, enter the formula

=IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))

Watch the line wrapping....

Copy that formula down as far as you need (to match your data rows), then copy across to match the
dates in row 3. You will get a table of the days for each month for each chasis number.

HTH,
Bernie
MS Excel MVP


"Bart" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I have a sheet with collums for entry date and exit date used in a car-
> compound. I also have a collumn for each month of the year.
>
> What I am trying to do is let excel (VBA) calculate how many days a
> car was on the compound in every month. This is a monthly/yearly
> ocurring item to check the invoicing.
>
> What I'd like to do is to pull data from our database into the excel
> sheet, just the chassis number, entry date and exit date. I would
> like to paste this in my excel, and then be presented with the amount
> of days a car was on the compound in each month.
>
> Is there any way to this ? I know that if it was a full month, it's
> easy to do, but let's say it has an entry on 13.01.06 and an exit on
> 20.10.06 , then it becomes more complicated. Also the calculation has
> to be performed on several thousand cars at once...
>
> Any help ??
>

Bernie,

first of all, thanks for your efforts in trying to find a solution !

When I use the formula though, it comes up with an error. See hxxp://
members.home.nl/hoenb/SNAG-0024.jpg for details on the error.

Many thanks in advance for your tip on solving the error.

Bart

On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> Bart,
>
> This solution assumes the following:
>
> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B, starting
> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column D,
> you have the dates for the first of the month, for the time period that you are interested in: Aug
> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you like...
>
> Then in cell D3, enter the formula
>
> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))
>
> Watch the line wrapping....
>
> Copy that formula down as far as you need (to match your data rows), then copy across to match the
> dates in row 3. You will get a table of the days for each month for each chasis number.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Bart" <bartma...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > Hi,
>
> > I have a sheet with collums for entry date and exit date used in a car-
> > compound. I also have a collumn for each month of the year.
>
> > What I am trying to do is let excel (VBA) calculate how many days a
> > car was on the compound in every month. This is a monthly/yearly
> > ocurring item to check the invoicing.
>
> > What I'd like to do is to pull data from our database into the excel
> > sheet, just the chassis number, entry date and exit date. I would
> > like to paste this in my excel, and then be presented with the amount
> > of days a car was on the compound in each month.
>
> > Is there any way to this ? I know that if it was a full month, it's
> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > to be performed on several thousand cars at once...
>
> > Any help ??

Bart,

Would you normally separate parts of a formula with a semi-colon? Depends on your regional
settings. Just change all the , to ; if that is your setting.

Also, it looks like you are going to have a lot of blank values for dates out. I would change the
formula to

=IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))



HTH,
Bernie
MS Excel MVP


"Bart" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bernie,
>
> first of all, thanks for your efforts in trying to find a solution !
>
> When I use the formula though, it comes up with an error. See hxxp://
> members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>
> Many thanks in advance for your tip on solving the error.
>
> Bart
>
> On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>> Bart,
>>
>> This solution assumes the following:
>>
>> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
>> starting
>> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column
>> D,
>> you have the dates for the first of the month, for the time period that you are interested in:
>> Aug
>> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
>> like...
>>
>> Then in cell D3, enter the formula
>>
>> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))
>>
>> Watch the line wrapping....
>>
>> Copy that formula down as far as you need (to match your data rows), then copy across to match
>> the
>> dates in row 3. You will get a table of the days for each month for each chasis number.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Bart" <bartma...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>>
>>
>> > Hi,
>>
>> > I have a sheet with collums for entry date and exit date used in a car-
>> > compound. I also have a collumn for each month of the year.
>>
>> > What I am trying to do is let excel (VBA) calculate how many days a
>> > car was on the compound in every month. This is a monthly/yearly
>> > ocurring item to check the invoicing.
>>
>> > What I'd like to do is to pull data from our database into the excel
>> > sheet, just the chassis number, entry date and exit date. I would
>> > like to paste this in my excel, and then be presented with the amount
>> > of days a car was on the compound in each month.
>>
>> > Is there any way to this ? I know that if it was a full month, it's
>> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
>> > 20.10.06 , then it becomes more complicated. Also the calculation has
>> > to be performed on several thousand cars at once...
>>
>> > Any help ??
>
>

Bernie,

true, we use semi-colon to seperate. Now it doesn't give an error,
but comes up with #NAME?

Any idea what causes this formula result ?

Thanks,
Bart


On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> Bart,
>
> Would you normally separate parts of a formula with a semi-colon? Depends on your regional
> settings. Just change all the , to ; if that is your setting.
>
> Also, it looks like you are going to have a lot of blank values for dates out. I would change the
> formula to
>
> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Bart" <bartma...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > Bernie,
>
> > first of all, thanks for your efforts in trying to find a solution !
>
> > When I use the formula though, it comes up with an error. See hxxp://
> > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>
> > Many thanks in advance for your tip on solving the error.
>
> > Bart
>
> > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> >> Bart,
>
> >> This solution assumes the following:
>
> >> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
> >> starting
> >> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column
> >> D,
> >> you have the dates for the first of the month, for the time period that you are interested in:
> >> Aug
> >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
> >> like...
>
> >> Then in cell D3, enter the formula
>
> >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))
>
> >> Watch the line wrapping....
>
> >> Copy that formula down as far as you need (to match your data rows), then copy across to match
> >> the
> >> dates in row 3. You will get a table of the days for each month for each chasis number.
>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
>
> >> "Bart" <bartma...@gmail.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > Hi,
>
> >> > I have a sheet with collums for entry date and exit date used in a car-
> >> > compound. I also have a collumn for each month of the year.
>
> >> > What I am trying to do is let excel (VBA) calculate how many days a
> >> > car was on the compound in every month. This is a monthly/yearly
> >> > ocurring item to check the invoicing.
>
> >> > What I'd like to do is to pull data from our database into the excel
> >> > sheet, just the chassis number, entry date and exit date. I would
> >> > like to paste this in my excel, and then be presented with the amount
> >> > of days a car was on the compound in each month.
>
> >> > Is there any way to this ? I know that if it was a full month, it's
> >> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> >> > 20.10.06 , then it becomes more complicated. Also the calculation has
> >> > to be performed on several thousand cars at once...
>
> >> > Any help ??

Bart,

AND, MAX, and IF are English language functions, and should return that error if they are called
something else in your specific version.

Also, you need to be using the A1 style of cell referencing...

HTH,
Bernie
MS Excel MVP


"Bart" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bernie,
>
> true, we use semi-colon to seperate. Now it doesn't give an error,
> but comes up with #NAME?
>
> Any idea what causes this formula result ?
>
> Thanks,
> Bart
>
>
> On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>> Bart,
>>
>> Would you normally separate parts of a formula with a semi-colon? Depends on your regional
>> settings. Just change all the , to ; if that is your setting.
>>
>> Also, it looks like you are going to have a lot of blank values for dates out. I would change
>> the
>> formula to
>>
>> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>> "Bart" <bartma...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > Bernie,
>>
>> > first of all, thanks for your efforts in trying to find a solution !
>>
>> > When I use the formula though, it comes up with an error. See hxxp://
>> > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>>
>> > Many thanks in advance for your tip on solving the error.
>>
>> > Bart
>>
>> > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>> >> Bart,
>>
>> >> This solution assumes the following:
>>
>> >> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
>> >> starting
>> >> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in
>> >> column
>> >> D,
>> >> you have the dates for the first of the month, for the time period that you are interested in:
>> >> Aug
>> >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
>> >> like...
>>
>> >> Then in cell D3, enter the formula
>>
>> >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))
>>
>> >> Watch the line wrapping....
>>
>> >> Copy that formula down as far as you need (to match your data rows), then copy across to match
>> >> the
>> >> dates in row 3. You will get a table of the days for each month for each chasis number.
>>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>>
>> >> "Bart" <bartma...@gmail.com> wrote in message
>>
>> >>news:(E-Mail Removed)...
>>
>> >> > Hi,
>>
>> >> > I have a sheet with collums for entry date and exit date used in a car-
>> >> > compound. I also have a collumn for each month of the year.
>>
>> >> > What I am trying to do is let excel (VBA) calculate how many days a
>> >> > car was on the compound in every month. This is a monthly/yearly
>> >> > ocurring item to check the invoicing.
>>
>> >> > What I'd like to do is to pull data from our database into the excel
>> >> > sheet, just the chassis number, entry date and exit date. I would
>> >> > like to paste this in my excel, and then be presented with the amount
>> >> > of days a car was on the compound in each month.
>>
>> >> > Is there any way to this ? I know that if it was a full month, it's
>> >> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
>> >> > 20.10.06 , then it becomes more complicated. Also the calculation has
>> >> > to be performed on several thousand cars at once...
>>
>> >> > Any help ??
>
>

Maybe you can try this.

Copy Bernie's formula (the post with the longgggg line).

Then off to excel.
Select the cell to get the formula

Then hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window.

Type this:
activecell.formula = "
(include the quotation mark)
Then hit ctrl-v to paste Bernie's formula.
(Don't worry about the final quote)

And then back to excel to look at that cell.

VBA is USA centric but excel will "translate" the USA formula into a formula
with your settings (and language).

Bart wrote:
>
> Bernie,
>
> true, we use semi-colon to seperate. Now it doesn't give an error,
> but comes up with #NAME?
>
> Any idea what causes this formula result ?
>
> Thanks,
> Bart
>
> On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > Bart,
> >
> > Would you normally separate parts of a formula with a semi-colon? Depends on your regional
> > settings. Just change all the , to ; if that is your setting.
> >
> > Also, it looks like you are going to have a lot of blank values for dates out. I would change the
> > formula to
> >
> > =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "Bart" <bartma...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> > > Bernie,
> >
> > > first of all, thanks for your efforts in trying to find a solution !
> >
> > > When I use the formula though, it comes up with an error. See hxxp://
> > > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
> >
> > > Many thanks in advance for your tip on solving the error.
> >
> > > Bart
> >
> > > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > >> Bart,
> >
> > >> This solution assumes the following:
> >
> > >> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
> > >> starting
> > >> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column
> > >> D,
> > >> you have the dates for the first of the month, for the time period that you are interested in:
> > >> Aug
> > >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
> > >> like...
> >
> > >> Then in cell D3, enter the formula
> >
> > >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3<=E$2),$C3-$B3,0)))))
> >
> > >> Watch the line wrapping....
> >
> > >> Copy that formula down as far as you need (to match your data rows), then copy across to match
> > >> the
> > >> dates in row 3. You will get a table of the days for each month for each chasis number.
> >
> > >> HTH,
> > >> Bernie
> > >> MS Excel MVP
> >
> > >> "Bart" <bartma...@gmail.com> wrote in message
> >
> > >>news:(E-Mail Removed)...
> >
> > >> > Hi,
> >
> > >> > I have a sheet with collums for entry date and exit date used in a car-
> > >> > compound. I also have a collumn for each month of the year.
> >
> > >> > What I am trying to do is let excel (VBA) calculate how many days a
> > >> > car was on the compound in every month. This is a monthly/yearly
> > >> > ocurring item to check the invoicing.
> >
> > >> > What I'd like to do is to pull data from our database into the excel
> > >> > sheet, just the chassis number, entry date and exit date. I would
> > >> > like to paste this in my excel, and then be presented with the amount
> > >> > of days a car was on the compound in each month.
> >
> > >> > Is there any way to this ? I know that if it was a full month, it's
> > >> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> > >> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > >> > to be performed on several thousand cars at once...
> >
> > >> > Any help ??

--

Dave Peterson

On 31 jan, 16:59, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Maybe you can try this.
>
> Copy Bernie's formula (the post with the longgggg line).
>
> Then off to excel.
> Select the cell to get the formula
>
> Then hit alt-f11 to get to the VBE (where macros live)
> hit ctrl-g to see the immediate window.
>
> Type this:
> activecell.formula = "
> (include the quotation mark)
> Then hit ctrl-v to paste Bernie's formula.
> (Don't worry about the final quote)
>
> And then back to excel to look at that cell.
>
> VBA is USA centric but excel will "translate" the USA formula into a formula
> with your settings (and language).
>
>
>
>
>
> Bart wrote:
>
> > Bernie,
>
> > true, we use semi-colon to seperate. Now it doesn't give an error,
> > but comes up with #NAME?
>
> > Any idea what causes this formula result ?
>
> > Thanks,
> > Bart
>
> > On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > > Bart,
>
> > > Would you normally separate parts of a formula with a semi-colon? Depends on your regional
> > > settings. Just change all the , to ; if that is your setting.
>
> > > Also, it looks like you are going to have a lot of blank values for dates out. I would change the
> > > formula to
>
> > > =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=*D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
>
> > > HTH,
> > > Bernie
> > > MS Excel MVP
>
> > > "Bart" <bartma...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > Bernie,
>
> > > > first of all, thanks for your efforts in trying to find a solution !
>
> > > > When I use the formula though, it comes up with an error. See hxxp://
> > > > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>
> > > > Many thanks in advance for your tip on solving the error.
>
> > > > Bart
>
> > > > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > > >> Bart,
>
> > > >> This solution assumes the following:
>
> > > >> Your chasis numbers are in column A, starting in row 3, your entrydates are in column B,
> > > >> starting
> > > >> in row 3, and your exit dates are in column C, starting in row 3. In row 2, starting in column
> > > >> D,
> > > >> you have the dates for the first of the month, for the time periodthat you are interested in:
> > > >> Aug
> > > >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
> > > >> like...
>
> > > >> Then in cell D3, enter the formula
>
> > > >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3*<=E$2),$C3-$B3,0)))))
>
> > > >> Watch the line wrapping....
>
> > > >> Copy that formula down as far as you need (to match your data rows), then copy across to match
> > > >> the
> > > >> dates in row 3. You will get a table of the days for each month for each chasis number.
>
> > > >> HTH,
> > > >> Bernie
> > > >> MS Excel MVP
>
> > > >> "Bart" <bartma...@gmail.com> wrote in message
>
> > > >>news:(E-Mail Removed)...
>
> > > >> > Hi,
>
> > > >> > I have a sheet with collums for entry date and exit date used ina car-
> > > >> > compound. I also have a collumn for each month of the year.
>
> > > >> > What I am trying to do is let excel (VBA) calculate how many days a
> > > >> > car was on the compound in every month. This is a monthly/yearly
> > > >> > ocurring item to check the invoicing.
>
> > > >> > What I'd like to do is to pull data from our database into the excel
> > > >> > sheet, just the chassis number, entry date and exit date. I would
> > > >> > like to paste this in my excel, and then be presented with the amount
> > > >> > of days a car was on the compound in each month.
>
> > > >> > Is there any way to this ? I know that if it was a full month, it's
> > > >> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> > > >> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > > >> > to be performed on several thousand cars at once...
>
> > > >> > Any help ??
>
> --
>
> Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Thanks guys,

I will give it a go tomorrow when I am @ work again, and let you know
my results.

Many thanks already, it looks like I will finally finish this
thinggy...

Bart

On 31 jan, 20:17, "Bart" <bartma...@gmail.com> wrote:
> On 31 jan, 16:59, Dave Peterson <peter...@verizonXSPAM.net> wrote:
>
>
>
> > Maybe you can try this.
>
> > Copy Bernie's formula (the post with the longgggg line).
>
> > Then off to excel.
> > Select the cell to get the formula
>
> > Then hit alt-f11 to get to the VBE (where macros live)
> > hit ctrl-g to see the immediate window.
>
> > Type this:
> > activecell.formula = "
> > (include the quotation mark)
> > Then hit ctrl-v to paste Bernie's formula.
> > (Don't worry about the final quote)
>
> > And then back to excel to look at that cell.
>
> > VBA is USA centric but excel will "translate" the USA formula into a formula
> > with your settings (and language).
>
> > Bart wrote:
>
> > > Bernie,
>
> > > true, we use semi-colon to seperate. Now it doesn't give an error,
> > > but comes up with #NAME?
>
> > > Any idea what causes this formula result ?
>
> > > Thanks,
> > > Bart
>
> > > On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > > > Bart,
>
> > > > Would you normally separate parts of a formula with a semi-colon? Depends on your regional
> > > > settings. Just change all the , to ; if that is your setting.
>
> > > > Also, it looks like you are going to have a lot of blank values fordates out. I would change the
> > > > formula to
>
> > > > =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=*D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
>
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
>
> > > > "Bart" <bartma...@gmail.com> wrote in message
>
> > > >news:(E-Mail Removed)...
>
> > > > > Bernie,
>
> > > > > first of all, thanks for your efforts in trying to find a solution !
>
> > > > > When I use the formula though, it comes up with an error. See hxxp://
> > > > > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>
> > > > > Many thanks in advance for your tip on solving the error.
>
> > > > > Bart
>
> > > > > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > > > >> Bart,
>
> > > > >> This solution assumes the following:
>
> > > > >> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
> > > > >> starting
> > > > >> in row 3, and your exit dates are in column C, starting in row 3.. In row 2, starting in column
> > > > >> D,
> > > > >> you have the dates for the first of the month, for the time period that you are interested in:
> > > > >> Aug
> > > > >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
> > > > >> like...
>
> > > > >> Then in cell D3, enter the formula
>
> > > > >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3*<=E$2),$C3-$B3,0)))))
>
> > > > >> Watch the line wrapping....
>
> > > > >> Copy that formula down as far as you need (to match your data rows), then copy across to match
> > > > >> the
> > > > >> dates in row 3. You will get a table of the days for each month for each chasis number.
>
> > > > >> HTH,
> > > > >> Bernie
> > > > >> MS Excel MVP
>
> > > > >> "Bart" <bartma...@gmail.com> wrote in message
>
> > > > >>news:(E-Mail Removed)...
>
> > > > >> > Hi,
>
> > > > >> > I have a sheet with collums for entry date and exit date used in a car-
> > > > >> > compound. I also have a collumn for each month of the year.
>
> > > > >> > What I am trying to do is let excel (VBA) calculate how many days a
> > > > >> > car was on the compound in every month. This is a monthly/yearly
> > > > >> > ocurring item to check the invoicing.
>
> > > > >> > What I'd like to do is to pull data from our database into theexcel
> > > > >> > sheet, just the chassis number, entry date and exit date. I would
> > > > >> > like to paste this in my excel, and then be presented with theamount
> > > > >> > of days a car was on the compound in each month.
>
> > > > >> > Is there any way to this ? I know that if it was a full month, it's
> > > > >> > easy to do, but let's say it has an entry on 13.01.06 and an exit on
> > > > >> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > > > >> > to be performed on several thousand cars at once...
>
> > > > >> > Any help ??
>
> > --
>
> > Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > - Tekst uit oorspronkelijk bericht weergeven -
>
> Thanks guys,
>
> I will give it a go tomorrow when I am @ work again, and let you know
> my results.
>
> Many thanks already, it looks like I will finally finish this
> thinggy...
>
> Bart

Guys,

many many thanks for the support, I tried it yesterday @home ( in my
english excel ) and it worked great. Saved the file, openened it
@work and it instantly worked !

Again, thanks a lot !
Bart

On 1 feb, 08:50, "Bart" <bartma...@gmail.com> wrote:
> On 31 jan, 20:17, "Bart" <bartma...@gmail.com> wrote:
>
>
>
> > On 31 jan, 16:59, Dave Peterson <peter...@verizonXSPAM.net> wrote:
>
> > > Maybe you can try this.
>
> > > Copy Bernie's formula (the post with the longgggg line).
>
> > > Then off to excel.
> > > Select the cell to get the formula
>
> > > Then hit alt-f11 to get to the VBE (where macros live)
> > > hit ctrl-g to see the immediate window.
>
> > > Type this:
> > > activecell.formula = "
> > > (include the quotation mark)
> > > Then hit ctrl-v to paste Bernie's formula.
> > > (Don't worry about the final quote)
>
> > > And then back to excel to look at that cell.
>
> > > VBA is USA centric but excel will "translate" the USA formula into a formula
> > > with your settings (and language).
>
> > > Bart wrote:
>
> > > > Bernie,
>
> > > > true, we use semi-colon to seperate. Now it doesn't give an error,
> > > > but comes up with #NAME?
>
> > > > Any idea what causes this formula result ?
>
> > > > Thanks,
> > > > Bart
>
> > > > On 31 jan, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> > > > > Bart,
>
> > > > > Would you normally separate parts of a formula with a semi-colon?Depends on your regional
> > > > > settings. Just change all the , to ; if that is your setting.
>
> > > > > Also, it looks like you are going to have a lot of blank values for dates out. I would change the
> > > > > formula to
>
> > > > > =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),MAX($C3-D$2+1,0),IF(AND($B3>=*D$2,$C3<=E$2),MAX($C3-$B3,0),0)))))
>
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
>
> > > > > "Bart" <bartma...@gmail.com> wrote in message
>
> > > > >news:(E-Mail Removed)...
>
> > > > > > Bernie,
>
> > > > > > first of all, thanks for your efforts in trying to find a solution !
>
> > > > > > When I use the formula though, it comes up with an error. See hxxp://
> > > > > > members.home.nl/hoenb/SNAG-0024.jpg for details on the error.
>
> > > > > > Many thanks in advance for your tip on solving the error.
>
> > > > > > Bart
>
> > > > > > On 30 jan, 17:12, "Bernie Deitrick" <deitbe @ consumer dot org>wrote:
> > > > > >> Bart,
>
> > > > > >> This solution assumes the following:
>
> > > > > >> Your chasis numbers are in column A, starting in row 3, your entry dates are in column B,
> > > > > >> starting
> > > > > >> in row 3, and your exit dates are in column C, starting in row3. In row 2, starting in column
> > > > > >> D,
> > > > > >> you have the dates for the first of the month, for the time period that you are interested in:
> > > > > >> Aug
> > > > > >> 1 06, Sep 1 06, etc. Format as custom mmmm yy to show just the month name and year, if you
> > > > > >> like...
>
> > > > > >> Then in cell D3, enter the formula
>
> > > > > >> =IF(AND($B3<D$2,$C3<D$2),0,IF(AND($B3<=D$2,$C3>=E$2),E$2-D$2,IF(AND($B3<=E$*2,$C3>=E$2),E$2-$B3,IF(AND($B3<=D$2,$C3<=E$2),$C3-D$2+1,IF(AND($B3>=D$2,$C3*<=E$2),$C3-$B3,0)))))
>
> > > > > >> Watch the line wrapping....
>
> > > > > >> Copy that formula down as far as you need (to match your data rows), then copy across to match
> > > > > >> the
> > > > > >> dates in row 3. You will get a table of the days for each month for each chasis number.
>
> > > > > >> HTH,
> > > > > >> Bernie
> > > > > >> MS Excel MVP
>
> > > > > >> "Bart" <bartma...@gmail.com> wrote in message
>
> > > > > >>news:(E-Mail Removed)...
>
> > > > > >> > Hi,
>
> > > > > >> > I have a sheet with collums for entry date and exit date used in a car-
> > > > > >> > compound. I also have a collumn for each month of the year.
>
> > > > > >> > What I am trying to do is let excel (VBA) calculate how manydays a
> > > > > >> > car was on the compound in every month. This is a monthly/yearly
> > > > > >> > ocurring item to check the invoicing.
>
> > > > > >> > What I'd like to do is to pull data from our database into the excel
> > > > > >> > sheet, just the chassis number, entry date and exit date. Iwould
> > > > > >> > like to paste this in my excel, and then be presented with the amount
> > > > > >> > of days a car was on the compound in each month.
>
> > > > > >> > Is there any way to this ? I know that if it was a full month, it's
> > > > > >> > easy to do, but let's say it has an entry on 13.01.06 and anexit on
> > > > > >> > 20.10.06 , then it becomes more complicated. Also the calculation has
> > > > > >> > to be performed on several thousand cars at once...
>
> > > > > >> > Any help ??
>
> > > --
>
> > > Dave Peterson- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > > - Tekst uit oorspronkelijk bericht weergeven -
>
> > Thanks guys,
>
> > I will give it a go tomorrow when I am @ work again, and let you know
> > my results.
>
> > Many thanks already, it looks like I will finally finish this
> > thinggy...
>
> > Bart
>
> Guys,
>
> many many thanks for the support, I tried it yesterday @home ( in my
> english excel ) and it worked great. Saved the file, openened it
> @work and it instantly worked !
>
> Again, thanks a lot !
> Bart

Hmmm there's just one little thing, it works for all months except if
a chassis has an exit date in December, then it comes up with the
number -39052 ??

Any tips ?

Thanks,
Bart