I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

Hi,

Am not sure i understood your question well. When you say a and b are
dates, do they include the respective time of that date as well. If so,
use this formula

=IF(A1="","",IF(HOUR(B1)10,DATEDIF(A1,B1,"D")+1,D ATEDIF(A1,B1,"D")))

where A1 is the checkin date & time and B1 is the Checkout date & time.

However, if you just store the dates in A1 and B1 and want to evaluate
the number of days based on the present time, use

IF(A13="","",IF(HOUR(NOW())10,DATEDIF(A13,B13,"D" )+1,DATEDIF(A13,B13,"D")))

However, be aware that the Now() function gets updated only when the
worksheet is calculated. It is not updated continuously.

Regards

Govind.

julieskennels wrote:
I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

Hi!

Try this:

=IF(OR(A1="",B1=""),"",IF(MOD(NOW(),1)10/24,B1-A1+1,B1-A1))

Note: the NOW function returns system date/time and updates whenever a
calculation takes place. It is not dynamic in that it is in sync with your
system clock.

For example enter NOW() in a formula. Wait a minute or two then hit function
key F9.

So, if you're looking at a clock and it's after 10 AM but the formula seems
to be not displaying the correct result......F9

Biff

"julieskennels" wrote in message
...
I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

j,

=IF(CheckOut-INT(CheckOut)=0.417361111110949,INT(CheckOut-CheckIn)+1,INT(CheckOut-CheckIn))

I named the in and out cells. Change the CheckIn and CheckOut to reflect
your cells.
7/14/05 5:30:00 PM 7/14/05 5:30:00 PM
7/17/05 10:01:00 AM 7/17/05 10:00:00 AM
3 Days 2 Days

This formula gives your customer's a one minute grace period, in other words
at 10:01 AM, it adds another day to the bill. I'm not sure how close you want
to cut it. Microsofts date is in the form of a serial number. The integer
portion is the month, day and year, while the decimal portion is the time.
You can change the decimal value in the formula to suit your own "grace
period"

Roy

"julieskennels" wrote:

I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

On Sun, 17 Jul 2005 19:37:02 -0700, "julieskennels"
wrote:
I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

=IF(ISBLANK(A1),"",IF(HOUR(NOW())9,(B1-A1)+1,B1-A1))

--
"Learning is a behavior that results from consequences."
B.F. Skinner

Forgot to blank out the total before check out. You can use a conditional
formula to white out the answer or put the formula inside an IF function...

=IF(CheckOut="","",Your Formula Here)

Roy

"julieskennels" wrote:

I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

Think an extra column for Checkout time would be required ..

Suppose you have this set-up in cols A to D, data from row2 down

DateIn DateOut TimeOut #OfDays
1-Jul-05 2-Jul-05 10:20 2
1-Jul-05 3-Jul-05 9:30 2
etc

where dates in/out are in cols A and B,
and checkout times are in col C (in "h:mm" format)

Then, to compute col D (#OfDays)

Put in D2:
=IF(OR(A2="",B2="",C2=""),"",IF(C2TIME(10,,),B2-A2+1,B2-A2))

Copy D2 down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"julieskennels" wrote in message
...
I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple

=IF(A1="","",B1-A1+(HOUR(NOW())10))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"julieskennels" wrote in message
...
I want to perform the following calculation.
a & b are dates; c is number of days to be charged for
Check out is 10 am
if a is blank ; cis blank
If current time is after 10am result is b-a+1
if current time is before 10am result is b-a
Having trouble with the time part - sure it is something simple