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.

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.

"David Biddulph" wrote:

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.

Presumably, then, the cells aren't actually empty. What does =LEN(A2) show?
Presumably not zero?
--
David Biddulph

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

"David Biddulph" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


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

"David Biddulph" wrote:

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.

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?

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


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

"David Biddulph" wrote:

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.

among others,

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

= if(A2<A1,1,0)+A2-A10.105167
should work over midnight

"stevieboy1313" wrote:

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?

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


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

"David Biddulph" wrote:

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.

=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.
--
David Biddulph

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

"Sandy Mann" wrote:

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.

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

"David Biddulph" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


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

"David Biddulph" wrote:

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.

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.

"bj" wrote:

among others,

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

= if(A2<A1,1,0)+A2-A10.105167
should work over midnight

"stevieboy1313" wrote:

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?

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


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

"David Biddulph" wrote:

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.

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

"bj" wrote:

among others,

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

= if(A2<A1,1,0)+A2-A10.105167
should work over midnight
....

I like it.

"David Biddulph" wrote:

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

"bj" wrote:

among others,

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

= if(A2<A1,1,0)+A2-A10.105167
should work over midnight
....