Formula for calculating storage days

[ShamsulZ]

Hi fren,

I m looking foR formula to calculate no of days in a month basis. Say :

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The answer for the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced

 

[Bob Phillips]

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[abcsms]

don't forget to plus one.

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[ShamsulZ]

Bob,

If i just substract I will get the no. of days for the whole period. In this
case, I want to use the formula in calculating the chargeable days in one
month as I billed my customer on monthly basis. In the case that I gave, 1-
14 sept is free, while 15-30th is chargeable and I already billed them. How
about from 1-31 oct? I m thinking to use if formula. Pls help..thanks in
advanced

 

[Bob Phillips]

Then we don't have enough info. How do we know that 1-14 sep is free and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[ShamsulZ]

Bob,

Ok what actually I m looking for in this case is one formula for me to use
every month for storage calculation. In this case, when the goods came in, we
will give a 14 days free storage. That's why I said 1-14 sept is free and
afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily basis for
say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20' or US 4
for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2 or US
4.

As this process is recurring, I m looking for one formula that can be used
every month. This will be lots of other scenarios such as cont in less than
14 days and we cant charged at all. Anyway, thanks a lot bob for your help

Shamsul

 

[Bob Phillips]

Shamsul,

Okay, so I get where the 14 free days come form, but taking your original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for me to use
every month for storage calculation. In this case, when the goods came in, we
will give a 14 days free storage. That's why I said 1-14 sept is free and
afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily basis for
say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20' or US 4
for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2 or US
4.

As this process is recurring, I m looking for one formula that can be used
every month. This will be lots of other scenarios such as cont in less than
14 days and we cant charged at all. Anyway, thanks a lot bob for your help

Shamsul

Then we don't have enough info. How do we know that 1-14 sep is free and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

In
this gave,
1- them.
How Say
: answer
for

 

[ShamsulZ]

Bob,

As I said previously I will bill my client on monthly basis. Thus as we are
now in Oct I already billed the sept. Assuming the same cont. and I want to
bill for Oct now as i did bill the sept. The free storage is for the 1st 14
days and day afterwards is chargeable. Thanks.

Shamsul,

Okay, so I get where the 14 free days come form, but taking your original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for me to use
every month for storage calculation. In this case, when the goods came in, we
will give a 14 days free storage. That's why I said 1-14 sept is free and
afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily basis for
say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20' or US 4
for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2 or US
4.

As this process is recurring, I m looking for one formula that can be used
every month. This will be lots of other scenarios such as cont in less than
14 days and we cant charged at all. Anyway, thanks a lot bob for your help

Shamsul

Then we don't have enough info. How do we know that 1-14 sep is free and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

If i just substract I will get the no. of days for the whole period. In
this
case, I want to use the formula in calculating the chargeable days in one
month as I billed my customer on monthly basis. In the case that I gave,
1-
14 sept is free, while 15-30th is chargeable and I already billed them.
How
about from 1-31 oct? I m thinking to use if formula. Pls help..thanks in
advanced

:

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi fren,

I m looking foR formula to calculate no of days in a month basis. Say
:

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The answer
for
the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced

 

[Bob Phillips]

Okay, try this formula

=IF(MONTH(A1)<>MONTH(B1),IF(B1-DAY(B1)+1-A1>14,DAY(B1),B1-A1-14),MAX(B1-A1-1
4,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

As I said previously I will bill my client on monthly basis. Thus as we are
now in Oct I already billed the sept. Assuming the same cont. and I want to
bill for Oct now as i did bill the sept. The free storage is for the 1st 14
days and day afterwards is chargeable. Thanks.

Shamsul,

Okay, so I get where the 14 free days come form, but taking your original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for me to use
every month for storage calculation. In this case, when the goods came

in,
we

will give a 14 days free storage. That's why I said 1-14 sept is free and
afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily

basis
for

say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20'

or
US 4

for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2

or
US

4.

As this process is recurring, I m looking for one formula that can be used
every month. This will be lots of other scenarios such as cont in less than
14 days and we cant charged at all. Anyway, thanks a lot bob for your help

Shamsul

:

Then we don't have enough info. How do we know that 1-14 sep is free and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

If i just substract I will get the no. of days for the whole

period.
In

this
case, I want to use the formula in calculating the chargeable days

in
one

month as I billed my customer on monthly basis. In the case that I gave,
1-
14 sept is free, while 15-30th is chargeable and I already billed them.
How
about from 1-31 oct? I m thinking to use if formula. Pls

help..thanks
in

advanced

:

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi fren,

I m looking foR formula to calculate no of days in a month

basis.
Say

:

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The answer
for
the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced

 

[ShamsulZ]

Ok thanks a lot I tried diff date and it works well. However, it is possible
to alter the formula if the goods not out at Oct.'06 which usually I will put
a symbol ' - ' means that as end at the month (Oct) the goods still stored at
our site. If we look at the formula the B1 cell must have some date on it
which blank or ' - ' will become an error.

Thanks a lot Bob for your assists.

Okay, try this formula

=IF(MONTH(A1)<>MONTH(B1),IF(B1-DAY(B1)+1-A1>14,DAY(B1),B1-A1-14),MAX(B1-A1-1
4,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

As I said previously I will bill my client on monthly basis. Thus as we are
now in Oct I already billed the sept. Assuming the same cont. and I want to
bill for Oct now as i did bill the sept. The free storage is for the 1st 14
days and day afterwards is chargeable. Thanks.

Shamsul,

Okay, so I get where the 14 free days come form, but taking your original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for me to use
every month for storage calculation. In this case, when the goods came in,
we
will give a 14 days free storage. That's why I said 1-14 sept is free and
afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily basis
for
say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20' or
US 4
for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2 or
US
4.

As this process is recurring, I m looking for one formula that can be used
every month. This will be lots of other scenarios such as cont in less
than
14 days and we cant charged at all. Anyway, thanks a lot bob for your help

Shamsul

:

Then we don't have enough info. How do we know that 1-14 sep is free and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

If i just substract I will get the no. of days for the whole period.
In
this
case, I want to use the formula in calculating the chargeable days in
one
month as I billed my customer on monthly basis. In the case that I
gave,
1-
14 sept is free, while 15-30th is chargeable and I already billed
them.
How
about from 1-31 oct? I m thinking to use if formula. Pls help..thanks
in
advanced

:

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi fren,

I m looking foR formula to calculate no of days in a month basis.
Say
:

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The
answer
for
the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced

 

[Bob Phillips]

Can you layout some data to illustrate this situation for me? Both
possibilities, with expected results please.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Ok thanks a lot I tried diff date and it works well. However, it is possible
to alter the formula if the goods not out at Oct.'06 which usually I will put
a symbol ' - ' means that as end at the month (Oct) the goods still stored at
our site. If we look at the formula the B1 cell must have some date on it
which blank or ' - ' will become an error.

Thanks a lot Bob for your assists.

Okay, try this formula

4,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

As I said previously I will bill my client on monthly basis. Thus as

we
are

now in Oct I already billed the sept. Assuming the same cont. and I

want
to

bill for Oct now as i did bill the sept. The free storage is for the

1st
14

days and day afterwards is chargeable. Thanks.

:

Shamsul,

Okay, so I get where the 14 free days come form, but taking your original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for

me to
use

every month for storage calculation. In this case, when the goods

came
in,

we
will give a 14 days free storage. That's why I said 1-14 sept is

free
and

afterward is chargeable. Say the complete pic is like this for container
business. There's two size 20' & 40' and will be charged on daily basis
for
say US 2 & US 4 respectively. In my example given previously, the charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for

20'
or

US 4
for 4'. However in Oct I will charged = [(15/10/06 -

01/10/06)+1]*US 2
or

US
4.

As this process is recurring, I m looking for one formula that can

be
used

every month. This will be lots of other scenarios such as cont in less
than
14 days and we cant charged at all. Anyway, thanks a lot bob for

your
help

Shamsul

:

Then we don't have enough info. How do we know that 1-14 sep is

free
and

15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

If i just substract I will get the no. of days for the whole period.
In
this
case, I want to use the formula in calculating the chargeable

days
in

one
month as I billed my customer on monthly basis. In the case that I
gave,
1-
14 sept is free, while 15-30th is chargeable and I already billed
them.
How
about from 1-31 oct? I m thinking to use if formula. Pls help..thanks
in
advanced

:

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi fren,

I m looking foR formula to calculate no of days in a

month
basis.

Say
:

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The
answer
for
the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced

 

[ShamsulZ]

Ok Bob 1st of all thanks a lot for helping me with this problem. The same
scenarios but in my previous case there is date in and date out. However, not
everytime the goods go out. E.g as below (assuming we are in end Oct.'06) :

Date In Date Out Chargeable days (monthly basis)

01/09/06 15/09/2006 1
01/09/06 15/10/2006 15
01/09/06 31/10/2006 31
01/09/06 - 31
30/10/06 - -

Still the same concept - 1st 14 days free storage, " - " means the goods
still at our site at the end of Oct.'06. The formula that you gave is
workable but when comes to the case that we dont know when the goods will go
out (" - " ) then it is a problem. Thanks a lot Bob.

Can you layout some data to illustrate this situation for me? Both
possibilities, with expected results please.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Ok thanks a lot I tried diff date and it works well. However, it is possible
to alter the formula if the goods not out at Oct.'06 which usually I will put
a symbol ' - ' means that as end at the month (Oct) the goods still stored at
our site. If we look at the formula the B1 cell must have some date on it
which blank or ' - ' will become an error.

Thanks a lot Bob for your assists.

Okay, try this formula

4,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

As I said previously I will bill my client on monthly basis. Thus as we
are
now in Oct I already billed the sept. Assuming the same cont. and I want
to
bill for Oct now as i did bill the sept. The free storage is for the 1st
14
days and day afterwards is chargeable. Thanks.

:

Shamsul,

Okay, so I get where the 14 free days come form, but taking your
original
example of

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

and you said,
1-14 sept is free, while 15-30th is chargeable and I already billed
them.

Where is the information that 15-30th Sep was already billed? Is it just
because we are now in Oct?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

Ok what actually I m looking for in this case is one formula for me to
use
every month for storage calculation. In this case, when the goods came
in,
we
will give a 14 days free storage. That's why I said 1-14 sept is free
and
afterward is chargeable. Say the complete pic is like this for
container
business. There's two size 20' & 40' and will be charged on daily
basis
for
say US 2 & US 4 respectively. In my example given previously, the
charges
would be on Sept. would be =[(30/09/06 - 01/09/06)+1-14]*US 2 for 20'
or
US 4
for 4'. However in Oct I will charged = [(15/10/06 - 01/10/06)+1]*US 2
or
US
4.

As this process is recurring, I m looking for one formula that can be
used
every month. This will be lots of other scenarios such as cont in less
than
14 days and we cant charged at all. Anyway, thanks a lot bob for your
help

Shamsul

:

Then we don't have enough info. How do we know that 1-14 sep is free
and
15-30 has been charged?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob,

If i just substract I will get the no. of days for the whole
period.
In
this
case, I want to use the formula in calculating the chargeable days
in
one
month as I billed my customer on monthly basis. In the case that I
gave,
1-
14 sept is free, while 15-30th is chargeable and I already billed
them.
How
about from 1-31 oct? I m thinking to use if formula. Pls
help..thanks
in
advanced

:

Just subtract the early date from the later date.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

Hi fren,

I m looking foR formula to calculate no of days in a month
basis.
Say
:

a) Date in = 01/09/2006
b) Date out = 15/10/2006
c) Free days = 14 days

I m looking for no of chargeable days for this purposes. The
answer
for
the
above is 15 days chargeable in Oct - monthly basis.

Thanks in advanced