Change a specific figure i.e 0:00 and 24:00 to display as "MIDT"

J

Jason M

I'm trying to get a cells to show the word "MIDT" when a time figures 0:00
and 24:00 are entered in the cell! Can anyone help me?
 
J

Jason M

I've tried that and still no joy :(
The cell is in P12 and I tried to alter the formula but still no joy. I want
to enter time format in cell P12 and be able to enter times between 0:00 and
23:59 but I want 0:00 to show as the word "MIDT". In cell R12 I want to be
able to enter times between 0:01 and 24:00 but I want 24:00 to show as the
word "MIDT". I would like a formula that will show an error message if in
cell P12 if the figure is greater than 23:59 and an error message in R12 if a
figure less than 0:01 and greater than 24:00. Cheers, it sounds totally
impossible but I hope and pray!
 
J

Jason M

PS Where would I enter these formulas?? i.e conditional formatting or cell
format?? Cheers
 
F

Fred Smith

You would enter it in a different cell. Why does it have to be the same one?

Regards,
Fred.
 
F

Fred Smith

You can display 0:00:00 as "MIDT" using a custom format of
hh:mm;hh:mm;"MIDT"

However, that won't work for for 24:00, because, to Excel, that's 1 day,
zero hours, zero minutes.

You can use custom formatting to check for values less than or greater than
certain values, but it will only highlight the cell, not display an error
message. What's wrong with using different cells for error messages and
special formatting?

Regards,
Fred.
 
J

Jason M

That worked perfect!! Shame I couldn't do anything so that 24:00 would show
as MIDT too! :( But thanks alot!! If by a very slim chance, you do come
across a formula that will change 24:00 to MIDT, then please let me know!
Cheers!!
 
F

Fred Smith

This should change both 24:00 and 0:00 to MIDT:

[=1]"MIDT";[=0]"MIDT";hh:mm

Regards
Fred
 
J

Jason M

That works like a treat!! Thanks for your help!!

Fred Smith said:
This should change both 24:00 and 0:00 to MIDT:

[=1]"MIDT";[=0]"MIDT";hh:mm

Regards
Fred

Jason M said:
That worked perfect!! Shame I couldn't do anything so that 24:00 would
show
as MIDT too! :( But thanks alot!! If by a very slim chance, you do come
across a formula that will change 24:00 to MIDT, then please let me know!
Cheers!!
Click to expand...
Click to expand...
 
F

Fred Smith

Glad I could help. Thanks for the feedback.

Fred.

Jason M said:
That works like a treat!! Thanks for your help!!

Fred Smith said:
This should change both 24:00 and 0:00 to MIDT:

[=1]"MIDT";[=0]"MIDT";hh:mm

Regards
Fred

Jason M said:
That worked perfect!! Shame I couldn't do anything so that 24:00 would
show
as MIDT too! :( But thanks alot!! If by a very slim chance, you do
come
across a formula that will change 24:00 to MIDT, then please let me
know!
Cheers!!

:

You can display 0:00:00 as "MIDT" using a custom format of
hh:mm;hh:mm;"MIDT"

However, that won't work for for 24:00, because, to Excel, that's 1
day,
zero hours, zero minutes.

You can use custom formatting to check for values less than or greater
than
certain values, but it will only highlight the cell, not display an
error
message. What's wrong with using different cells for error messages
and
special formatting?

Regards,
Fred.

I've tried that and still no joy :(
The cell is in P12 and I tried to alter the formula but still no
joy. I
want
to enter time format in cell P12 and be able to enter times between
0:00
and
23:59 but I want 0:00 to show as the word "MIDT". In cell R12 I want
to
be
able to enter times between 0:01 and 24:00 but I want 24:00 to show
as
the
word "MIDT". I would like a formula that will show an error message
if
in
cell P12 if the figure is greater than 23:59 and an error message in
R12
if a
figure less than 0:01 and greater than 24:00. Cheers, it sounds
totally
impossible but I hope and pray!

:

=if(mod(a1,1)=0,"MIDT","Not MIDT")

Regards,
Fred.

I'm trying to get a cells to show the word "MIDT" when a time
figures
0:00
and 24:00 are entered in the cell! Can anyone help me?
Click to expand...
Click to expand...
Click to expand...
 
Top