"Time between" calculated over midnight

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I need to compair an inputted time (A1) to 2 columns of
calculated times: labeled "zone start" (C3), and "zone
stop" (D3). Only one "E" columnn entry is possible at a
time.

currently using:
If(AND(A$1>=C3,A$1<D3),E3,0)

Once this passes midnight it stops working.
Thanks
Brad
 
You have not adequatly described what you want. Presumably you want
11:00 PM >= 11:30 PM
to return False, but you want
12:30 AM >= 11:30 PM
to return True. Where do you want to draw the line?

One approach that may not be adequate for your situation would be to use
something like
OR(A$1>=C3,1+A$1-C3<0.5)
instead of A$1>=C3. This assumes that if midnight was crossed, the
elapsed time should be less than 12 hours.

Jerry
 
Like Jerry, I'm not really sure what you're asking. Once which of the numbers passes midnight
At any rate, it sounds to me like you can fix the problem by altering the data you enter into the cells and changing how the cells are formatted
Use a Time format to display the times as you wish them to appear in the cells
--However-
Instead of entering simply the time, type the date followed by the time. Example
4/25/04 11:00am
If you format it as time (Format|Cells, choose a Time format), the date won't appear in the cell. But, when I typ
4/26/04 12:30am Excel will now recognize it as larger than the time 4/25/04 11:00am

Unless you tell it otherwise, Excel assumes all the times you are working with are from the same 24 hour period. So, what probably happened is that when you thought you had 'passed midnight,' Excel thought you were talking about 24hrs. earlier than what you meant

t
 
Jerry and TJTJTJ, et.al.

First Thank You for the help. . .

What I would like is to compaire my A1 manually inputted
time cell and determine if it is between my "start" C4
and my "stop" D4 times. As long as this is "TRUE"
then "E4" otherwise zero. These C/D columns could span
midnight, and my inputted times A1 could be anytime from
7:30 pm to 8:00 am the next day.
As long as A1 is less than 12:00 am and the Start-Stop
interval doesn't span midnight the =IF(AND
(true,true),E#,0) form will work however as soon as "C/D"
columns span 12:00 am the AND portion will return a false
though visually A1 (12:05 am say) is between the "start"
of 11:35 pm (say) and "stop" 12:41 am (say).
If I were to "hard code" the full date and time in the
Program Start how would I continue to update the date and
time when I next need to change run times?

Eventually, this spreadsheet should emulate an individual
golf course irrigation control satellite where I would
input the Program Start times (times 4
Programs "A", "B", "C" and "D") and individual zone run
times(0 to 60 min. each, and 6 to 25 zones per program).
The "E" column is the gallons per minute for that
indivudual zone. When finished I will have 23 satellites
each with A,B,C,D programs. What I wish is to "dry run"
these 23 controllers and their programs so I can graph
the results of what happens (in gallons per minute) when
individual zone run times are changed.


Brad

..
-----Original Message-----
Like Jerry, I'm not really sure what you're asking. Once
Click to expand...
which of the numbers passes midnight?
At any rate, it sounds to me like you can fix the
Click to expand...
problem by altering the data you enter into the cells and
changing how the cells are formatted.
Use a Time format to display the times as you wish them to appear in the cells.
--However--
Instead of entering simply the time, type the date followed by the time. Example:
4/25/04 11:00am.
If you format it as time (Format|Cells, choose a Time
Click to expand...
format), the date won't appear in the cell. But, when I
type
4/26/04 12:30am Excel will now recognize it as larger than the time 4/25/04 11:00am.

Unless you tell it otherwise, Excel assumes all the
Click to expand...
times you are working with are from the same 24 hour
period. So, what probably happened is that when you
thought you had 'passed midnight,' Excel thought you were
talking about 24hrs. earlier than what you meant.
 
Possibly

=IF(D3<C3,IF(A$1>=C3,E3,IF(A$1<=D3,E3,0)),IF(AND(A$1>=C3,A$1<D3),E3,0))

Jerry
 
Back
Top