| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change a specific figure i.e 0:00 and 24:00 to display as "MIDT"
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MIDT"
=if(mod(a1,1)=0,"MIDT","Not MIDT")
Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
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! "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
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. "Jason M" wrote in message ... 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! "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
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!! "Fred Smith" wrote: 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. "Jason M" wrote in message ... 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! "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
This should change both 24:00 and 0:00 to MIDT:
[=1]"MIDT";[=0]"MIDT";hh:mm Regards Fred "Jason M" wrote in message ... 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!! "Fred Smith" wrote: 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. "Jason M" wrote in message ... 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! "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
PS Where would I enter these formulas?? i.e conditional formatting or cell
format?? Cheers "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Change a specific figure i.e 0:00 and 24:00 to display as "MID
You would enter it in a different cell. Why does it have to be the same one?
Regards, Fred. "Jason M" wrote in message ... PS Where would I enter these formulas?? i.e conditional formatting or cell format?? Cheers "Fred Smith" wrote: =if(mod(a1,1)=0,"MIDT","Not MIDT") Regards, Fred. "Jason M" <Jason wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation ?:Accepting both Numbers AND specific letters("N","n"," | Excel Discussion (Misc queries) | |||
| How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Change the "Insert Function" display for mail merge | Excel Worksheet Functions | |||
| How to change the font of Display window of "Open File" | Excel Discussion (Misc queries) | |||
![Jason M[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
