Formula for calculating storage days

S

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
 
B

Bob Phillips

Just subtract the early date from the later date.

--
HTH

Bob Phillips

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

abcsms

don't forget to plus one.

Bob Phillips said:
Just subtract the early date from the later date.

--
HTH

Bob Phillips

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

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
 
B

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

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
 
B

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)

ShamsulZ said:
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 said:
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
 
S

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.

Bob Phillips said:
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)

ShamsulZ said:
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 said:
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
 
B

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)

ShamsulZ said:
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.

Bob Phillips said:
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)

ShamsulZ said:
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
 
S

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.

Bob Phillips said:
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)

ShamsulZ said:
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.

Bob Phillips said:
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
 
B

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)

ShamsulZ said:
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.

Bob Phillips said:
Okay, try this formula
=IF(MONTH(A1) said:
4,0))

--
HTH

Bob Phillips

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

ShamsulZ said:
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
 
S

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.

Bob Phillips said:
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)

ShamsulZ said:
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.

Bob Phillips said:
Okay, try this formula
=IF(MONTH(A1) said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top