Military Time Conditional Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, what formula could I use to set conditional formatting on a page that
has 24 hour time for the entries? For example, if the entry in A1 is 1900,
and the next entry is due at 2100, how can I get the spreadsheet to show a
warning if the entry is early? Or if it's more than 30 minutes late late?

I'm using XP Pro and Office 2003, and I'm a beginner, so please keep it
simple.
 
Military time and 24 hour time is different, 24 hour time is when 6:00 PM is
written 18:00.
It is the default Excel time, military time like 1900 is different, you
would need to convert it to real time values before you can make any
calculations with 30 minutes etc since 1900 is 1900 to Excel, not a time
value. To convert 1900 to 19:00 you can use this formula


=--(TEXT(A1,"00\:00"))


assuming A1 holds 1900 and you need to format the cell with the formula as
hh:mm

Where would the next entry be, in A2 and do you want to calculate if the
next entry is earlier than 21:00 or if it is later than or equal to 21:30?

=IF(--(TEXT(A2,"00\:00"))<TIME(21,0,0),"Early",IF(TEXT(A2,"00\:00")-TIME(0,30,0)>=TIME(21,0,0),"Late","On
Time"))
 
Is it truely time? You can check by changing the format and seeing if the
display changes.

what type of warning do you want?

validation can be used to give a message if the data does not meet certain
criteria

conditonal formating could be used to change the color based on certain
criteria

A simple if statement could be used in an adjacent cell to give different
messages.

More info on what you want please.
 
Thanks for the reply. Yes, it is time, and we use the 24 hour format. It is
for a lab that makes scheduled QA pulls, and I want to put the tracking logs
on a PC. So, I need to conditional format the tracking log (spreadsheet) that
would turn a cell red if they enter a pull time that is too early. A late
pull time warning would be nice too, but the early pulls are what I am
especially concerned about. I'm playing with the formula given in the first
answer, which makes sense, but I can't figure out how to make it work for
conditional formatting.
 
since time is recorded with one day as a 1 increment
2 hours is 0.83333
2.5 hours is 0.104167
if the two times you are comparing are in cells A2 and A1
select A2
format-conditonal format
change value is to formula is
=A2-A1<0.083333 format pattern as red
condition 2
formula is = A2-A1>0.105167 format pattern as another color
 
BJ, Thanks so much. The formula works great, on both conditions. But, Every
cell that I copy the formatting into turns red even while empty. How can I
change that?
 
make current conditions 1 and 2 be conditions 2 and 3
make new condtion 1
value is ""
format as no pattern
 
Well, thank you very much, but I still can't get either one of these to work.
BJ's formula works, but it still turns the empty cells red.
 
Presumably, then, the cells aren't actually empty. What does =LEN(A2) show?
Presumably not zero?
 
Are you copying the formula from the post or buildingit from scratch? If
you are building it yourself then it will be entered as absolute references
like:

=AND($A$2<>"",$A$2-$A$1<0.083333)

which will account for the cell turning red.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ok, awesome, thank you all very much. I think it works now, except that I
think I have one more issue. Let's say that I post a time at 23:00 hrs(11pm),
and my next time is due at 01:00(1am). It doesn't seem to work for that. Is
there a way to cross the "date line", or do we need to start a new
spreadsheet every day at midnight?
 
among others,

=if(A2<A1,1,0)+A2-A1<0.083333

= if(A2<A1,1,0)+A2-A1>0.105167
should work over midnight
 
=AND(A2<>"",MOD(A2-A1,1)<0.083333) should address your midnight problem.

Glad that you found the previous problem after Sandy pointed you in the
right direction. With CF it's always worth checking that you've got the
formula that you intended, as it is very prone to throwing in unwanted quote
marks and absolute references. In general it is less inclined to do so if
you type in the = sign yourself rather than letting Excel add it for you.
 
Just to give you another option:

=AND(A2<>"",mod(A2-A1,1)<0.083333)

also works over midnight.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
It does work. Thank you all very much. It is so nice to have a place to go
and get answers from the experts. I am very grateful.
 
Glad it works. bj's formulae can of course be simplified, so
=if(A2<A1,1,0)+A2-A1<0.083333 could be just =(A2<A1)+A2-A1<0.083333
--
David Biddulph

stevieboy1313 said:
It does work. Thank you all very much. It is so nice to have a place to go
and get answers from the experts. I am very grateful.
Click to expand...
....
 
I like it.

David Biddulph said:
Glad it works. bj's formulae can of course be simplified, so
=if(A2<A1,1,0)+A2-A1<0.083333 could be just =(A2<A1)+A2-A1<0.083333
--
David Biddulph



....
Click to expand...
 
Back
Top