perform calculation only if current time is after 10 am

J

julieskennels

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
 
G

Govind

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,DATEDIF(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.
 
B

Biff

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
 
R

Roy

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
 
M

Mangus Pyke

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
Click to expand...

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

Roy

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
 
M

Max

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(C2>TIME(10,,),B2-A2+1,B2-A2))

Copy D2 down
 
Top