Home |
Search |
Today's Posts |
#1
|
|||
|
|||
perform calculation only if current time is after 10 am
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
how do i insert the current time into a cell, and show different . | Excel Discussion (Misc queries) | |||
Time Calculation | Excel Worksheet Functions | |||
elapsed time calculation | Excel Discussion (Misc queries) | |||
Time calculation. | Excel Worksheet Functions |