You can save a little bit of extra space (the width of the letter M) by Custom Formatting the cells using this type pattern...Shucks, I am trying to write train schedules and need the cell size to be minimized so i can fit tons of info on one page.
One question though, my "Public" worksheet when using this method displays 1:30 pm as 01:30. Do you know a good way to drop the zero's from 1-9?
01:00 | 100 | ||||||||||||||||
02:00 | 200 | ||||||||||||||||
problem statement - make 18:00 typed in as 18:00 appear as 600 | 03:00 | 300 | |||||||||||||||
04:00 | 400 | ||||||||||||||||
timetable for typing | 05:00 | 500 | |||||||||||||||
06:00 | 600 | ||||||||||||||||
18:00 | 07:00 | 700 | |||||||||||||||
08:00 | 800 | ||||||||||||||||
09:00 | 900 | ||||||||||||||||
10:00 | 1000 | ||||||||||||||||
11:00 | 1100 | ||||||||||||||||
12:00 | 1200 | ||||||||||||||||
13:00 | 100 | ||||||||||||||||
timetable for actual use | 14:00 | 200 | |||||||||||||||
15:00 | 300 | ||||||||||||||||
600 | 16:00 | 400 | |||||||||||||||
17:00 | 500 | ||||||||||||||||
=VLOOKUP($E$8,mytable,2) | 18:00 | 600 | |||||||||||||||
19:00 | 700 | ||||||||||||||||
20:00 | 800 | ||||||||||||||||
21:00 | 900 | ||||||||||||||||
22:00 | 1000 | ||||||||||||||||
23:00 | 1100 | ||||||||||||||||
00:00 | 1200 | ||||||||||||||||
you could make a table from 00:01 to 23:59 | |||||||||||||||||
and define thus how you want any time to display | |||||||||||||||||
00:19 would be 019 I guess | |||||||||||||||||
the above table is named mytable | |||||||||||||||||
Where did you see this at? Note that this thread restarted at Message #24 .problem statement - make 18:00 typed in as 18:00 appear as 600
dkmahan this is the best and most awesomestest solution.Sorry. I can't get my cell formula to display after the less than sign. I'll try putting the less than sign in quotes. Of course if the quotes display, you do not need the quotes around the actual less than sign:
IF(A1"<"TIME(13,0,0),A1,A1-TIME(12,0,0)).
Great answer! Thank you. Option 2 worked great for my needs. Keeping the information as a time value is very handy.Hi istril
There is no number format that can directly diplay 18:00 as 6:00, they are not, in fact, the same time.
You'll have to find some workaround that may suit you. For ex., 2 options:
1 -use in another cell a formula, like
=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"AM",""),"PM","")
Notice that the result in the cell is a string, not a time value, and you lose the information if it is am/pm
2 - hide the am/pm part in a second line
You can format the cell with a format this way.´
In the Custom Format write
. h:mmAM/PM
. position the cursor between the h:mm and the AM/PM
. press CTRL-J
Format the cell with wrap text
This format sends the AM/PM part to a second line. By keeping the default row height, this second line is not visible and so it's like you have the format you wanted.
Notice that in this case you still have the time value in the cell, you lost no information.