Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....

You can use conditional formatting to do this, but it might be a bit
awkward for you - if you enter 3:00 in cell then Excel will take it to
be 3:00am, and so assuming you also get faxes between 8:00am and noon
you will have to be a bit careful about the CF formula.

Hope this helps.

Pete

On Sep 19, 4:30 pm, dave@penneys
wrote:
Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....

Pete actually we don't get faxes before 12:00 noon the cut off times for the
faxes are 3pm and 6pm... we enter both times.

Dave

"Pete_UK" wrote:

You can use conditional formatting to do this, but it might be a bit
awkward for you - if you enter 3:00 in cell then Excel will take it to
be 3:00am, and so assuming you also get faxes between 8:00am and noon
you will have to be a bit careful about the CF formula.

Hope this helps.

Pete

On Sep 19, 4:30 pm, dave@penneys
wrote:
Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....

OK, you could highlight both conditions using conditional formatting.
Assuming your times are in column A starting at A1, highlight all the
cells from A1 downwards and then click on Format | Conditional
Formatting. In the panel that pops up select Formula is rather than
Cell Contents Is and enter this formula:

=A1=TIMEVALUE("6:00")

then click on the format button, select the Patterns tab and choose
something like bright green. Click OK and then click Add to set up a
second condition. Choose Formula Is again in the second CF panel and
enter this formula:

=A1TIMEVALUE("3:00")

click the Format button, Patterns tab and choose a different colour,
eg Red. Click OK twice to get out of the CF panel and you should now
find all times beyond 5:59 are highlighted in green and all times
between 3:00 and 5:59 are highlighted in red - stands out more clearly
than just Bold.

Sometimes Excel will try to help and put quotes around the formulae,
so if you do not get the effects then highlight the cells again
starting with A1 and Format | Conditional Formatting to check that the
formulae are exactly as above - delete any extra quotes.

If you want to apply this to other cells you can use the Format
Painter.

Hope this helps.

Pete

On Sep 19, 6:44 pm, dave@penneys
wrote:
Pete actually we don't get faxes before 12:00 noon the cut off times for the
faxes are 3pm and 6pm... we enter both times.

Dave



"Pete_UK" wrote:
You can use conditional formatting to do this, but it might be a bit
awkward for you - if you enter 3:00 in cell then Excel will take it to
be 3:00am, and so assuming you also get faxes between 8:00am and noon
you will have to be a bit careful about the CF formula.

Hope this helps.

Pete

On Sep 19, 4:30 pm, dave@penneys
wrote:
Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....- Hide quoted text -

- Show quoted text -

Hiya Pete,

worked like a charm.. thanks...

"Pete_UK" wrote:

OK, you could highlight both conditions using conditional formatting.
Assuming your times are in column A starting at A1, highlight all the
cells from A1 downwards and then click on Format | Conditional
Formatting. In the panel that pops up select Formula is rather than
Cell Contents Is and enter this formula:

=A1=TIMEVALUE("6:00")

then click on the format button, select the Patterns tab and choose
something like bright green. Click OK and then click Add to set up a
second condition. Choose Formula Is again in the second CF panel and
enter this formula:

=A1TIMEVALUE("3:00")

click the Format button, Patterns tab and choose a different colour,
eg Red. Click OK twice to get out of the CF panel and you should now
find all times beyond 5:59 are highlighted in green and all times
between 3:00 and 5:59 are highlighted in red - stands out more clearly
than just Bold.

Sometimes Excel will try to help and put quotes around the formulae,
so if you do not get the effects then highlight the cells again
starting with A1 and Format | Conditional Formatting to check that the
formulae are exactly as above - delete any extra quotes.

If you want to apply this to other cells you can use the Format
Painter.

Hope this helps.

Pete

On Sep 19, 6:44 pm, dave@penneys
wrote:
Pete actually we don't get faxes before 12:00 noon the cut off times for the
faxes are 3pm and 6pm... we enter both times.

Dave



"Pete_UK" wrote:
You can use conditional formatting to do this, but it might be a bit
awkward for you - if you enter 3:00 in cell then Excel will take it to
be 3:00am, and so assuming you also get faxes between 8:00am and noon
you will have to be a bit careful about the CF formula.

Hope this helps.

Pete

On Sep 19, 4:30 pm, dave@penneys
wrote:
Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....- Hide quoted text -

- Show quoted text -

You're welcome, Dave - thanks for feeding back.

Pete

On Sep 20, 6:58 pm, dave@penneys
wrote:
Hiya Pete,

worked like a charm.. thanks...



"Pete_UK" wrote:
OK, you could highlight both conditions using conditional formatting.
Assuming your times are in column A starting at A1, highlight all the
cells from A1 downwards and then click on Format | Conditional
Formatting. In the panel that pops up select Formula is rather than
Cell Contents Is and enter this formula:

=A1=TIMEVALUE("6:00")

then click on the format button, select the Patterns tab and choose
something like bright green. Click OK and then click Add to set up a
second condition. Choose Formula Is again in the second CF panel and
enter this formula:

=A1TIMEVALUE("3:00")

click the Format button, Patterns tab and choose a different colour,
eg Red. Click OK twice to get out of the CF panel and you should now
find all times beyond 5:59 are highlighted in green and all times
between 3:00 and 5:59 are highlighted in red - stands out more clearly
than just Bold.

Sometimes Excel will try to help and put quotes around the formulae,
so if you do not get the effects then highlight the cells again
starting with A1 and Format | Conditional Formatting to check that the
formulae are exactly as above - delete any extra quotes.

If you want to apply this to other cells you can use the Format
Painter.

Hope this helps.

Pete

On Sep 19, 6:44 pm, dave@penneys
wrote:
Pete actually we don't get faxes before 12:00 noon the cut off times for the
faxes are 3pm and 6pm... we enter both times.

Dave

"Pete_UK" wrote:
You can use conditional formatting to do this, but it might be a bit
awkward for you - if you enter 3:00 in cell then Excel will take it to
be 3:00am, and so assuming you also get faxes between 8:00am and noon
you will have to be a bit careful about the CF formula.

Hope this helps.

Pete

On Sep 19, 4:30 pm, dave@penneys
wrote:
Newbie here.... working on a spreadsheet that we need to enter times that we
receive faxes from our 3rd party... we enter times in this format 3:30 is
3:30 pm. the workbook is shared on a network there are several people
entering data at one time... what i need to know is can i change to
text(time) that is entered into a cell if the time is after the the required
time. example all our faxes are to sent to us prior to 3:00pm daily we use
the time stamp from the fax machine as the time we received the fax if the
time stamp is after 3:00pm say 3:20 I need the 3:20 that is entered to become
BOLD face for the fax was receive after the deadline of 3:00pm?

is there a way that i can program this to work?

thanks in advance

Dave....- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -