Days per month for calculating storage days

[Bart]

Hello there,

can anyone help me solve the error I get when I enter the below given
formula ?

Any help is much appreciated.

Thanks,
Bart

********************************

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


- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -

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

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.

 

[Bob Phillips]

Do you have a continental version of Excel? If so, try

=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)))))

If that doesn't work, give an example of a simple IF formula that does work
for you, and your language, it may need different functions.

Also make sure that D2, E2, etc are start of month dates as Bern ie assumed,
not text.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bart]

Bob,

I use a Dutch version of excel, but your variant also comes up with
the same error. Date is ( for January ) 01-01-2006 formatted as mmmm-
y so visible as january-06

Simple example of working if function :
=ALS(ISGETAL(C3);C3*10;"ERROR") [ if it's a number, calculate, else
display the text ERROR ]


Thanks for feedback.
Bart

 

[Bob Phillips]

So try this one Bart

=ALS(EN($B3<D$2,$C3<D$2);0;
ALS(EN($B3<=D$2,$C3>=E$2);E$2-D$2;ALS(EN($B3<=E$2;$C3>=E$2);E$2-$B3;
ALS(EN($B3<=D$2,$C3<=E$2);$C3-D$2+1;ALS(EN($B3>=D$2;$C3<=E$2);$C3-$B3;0)))))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

I use a Dutch version of excel, but your variant also comes up with
the same error. Date is ( for January ) 01-01-2006 formatted as mmmm-
y so visible as january-06

Simple example of working if function :
=ALS(ISGETAL(C3);C3*10;"ERROR") [ if it's a number, calculate, else
display the text ERROR ]


Thanks for feedback.
Bart

Do you have a continental version of Excel? If so, try

=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 that doesn't work, give an example of a simple IF formula that does work
for you, and your language, it may need different functions.

Also make sure that D2, E2, etc are start of month dates as Bern ie assumed,
not text.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)









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 copy across to match the
each
chasis number.