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(A 2,"00\:00")-TIME(0,30,0)=TIME(21,0,0),"Late","On
Time"))



--
Regards,

Peo Sjoblom





"stevieboy1313" wrote in message
...
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.

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.

"stevieboy1313" wrote:

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.

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.

"bj" wrote:

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.

"stevieboy1313" wrote:

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.

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-A10.105167 format pattern as another color

"stevieboy1313" wrote:

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.

"bj" wrote:

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.

"stevieboy1313" wrote:

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.

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?

"bj" wrote:

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-A10.105167 format pattern as another color

"stevieboy1313" wrote:

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.

"bj" wrote:

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.

"stevieboy1313" wrote:

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.

Try changing your formula from
=A2-A1<0.083333
to
=AND(A2<"",A2-A1<0.083333)
--
David Biddulph

"stevieboy1313" wrote in message
...
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?

"bj" wrote:

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-A10.105167 format pattern as another color

"stevieboy1313" wrote:

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.

"bj" wrote:

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.

"stevieboy1313" wrote:

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.

make current conditions 1 and 2 be conditions 2 and 3
make new condtion 1
value is ""
format as no pattern

"stevieboy1313" wrote:

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?

"bj" wrote:

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-A10.105167 format pattern as another color

"stevieboy1313" wrote:

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.

"bj" wrote:

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.

"stevieboy1313" wrote:

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.