Time Format

Microsoft Office excel 2007 home & stude...
June 7, 2010 at 14:40:56
Specs: Windows 7
I am trying to be able to type in the number 8-11
and have it format it to 8:00 AM, 9:00 AM and so
on automatically or for 12 - 6 have it format to
12:00 PM also. Every time I type in 8 it screws
up. I have tried many formulas to do this, but
cannot get it to work. Is this even possible?

See More: Time Format

Report •

June 7, 2010 at 15:16:23

When you say you are trying to type in the number 8-11, do you mean that you are typing '8-11'.

If you are entering =8-11, Excel will assume that you are entering 8 minus 11 which is -3
If you enter '8-11', Excel will assume that you are entering a date and return 8 November.
However what shows in the cell will also depend on how you have formatted the cell.
If the format is hh:mm you will get ###### for -3 as Excel will not accept negative times, or 00:00 for 8 Nov as there was no time entered (as far as Excel was concerned)

If you want to enter a time in a previously unformatted cell, enter it as 8:11 which will be recognized as a time and if formatted as hh:mm will show as 08:11.
If formatted as h:mm AM/PM it would show as 8:11 AM

Can you explain why you expected 8-11 to show as 8:00 AM and not 8:11 AM or 12-6 as 12:00AM

You also say I have tried many formulas to do this.
can you post what you have tried.

Usually just entering a time as 12:06 or 11:45 with no formula, no equals sign and just a colon between the numbers works OK. Then adjust the format using the normal formatting, such as h:mm AM/PM or hh:mm

As an aside, Excel stores times as a decimal value. 0.5 is 12 noon, i.e., half way through the day. Dates are stored as the whole number part of the value, so 40336 is 07, July 2010 (1 was 01 January 1900 in Excel running in Windows OS's)
40336.645833 is 15:30 on 07, July 2010


Report •

June 7, 2010 at 15:20:30
I mean if I type 8 as the time or 9 or 10 ... that it will convert it to
8:00 AM, or the same for 12, 1, 2, 3, except it converts it to
12:00 PM

Report •

June 7, 2010 at 15:40:29
Not sure of this but have you tried:

On the the Task Bar,
Number Tab

Select the time format you want.

Then you must type at a minimum 8:
that's eight with a colon.



Report •

Related Solutions

June 7, 2010 at 15:45:38
That works, but not exactly what I want to do

Report •

June 7, 2010 at 17:10:25
That works, but not exactly what I want to do

You can not simply enter the number 8, else Excel thinks it's a number, even with the formatting as Time.

There may be a VBA solution, but I'm not the one who would know, as my VBA skills are just above nill.



Report •

June 7, 2010 at 17:33:49
I have tried many formulas to do this,

If your looking for a formula, try this:

In Cell B1 format the cell as TIME, AM/PM
also enter the formula:

Now if you enter the number 8 in cell A1
cell B1 will give you the time as 8:00 AM,

if you want 8:00 PM
you will have to enter the number 20 into cell A1.



Report •

June 7, 2010 at 17:42:14
Right click the sheet tab for the sheet you want this to happen in and paste in the code below.

When you enter one of your numbers in Column A, it should appear as you want it to.

Change this line if Column A it not where you want this to happen:

If Target.Column = 1 Then...

(1 = A, 2 = B, etc)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EventsOn
'Do nothing if more than one cell was changed.
 If Target.Cells.Count > 1 Then Exit Sub
'Disable Events so Macro doesn't trigger Change Event
  Application.EnableEvents = False
'Check if Change was to Column 1
   If Target.Column = 1 Then
'If, yes, set format to General
    Target.NumberFormat = "General"
'If 1 - 6, make it PM
    Select Case Target.Value
     Case 1 To 6
      Target = Target + 12 & ":"
      Target.NumberFormat = "h:mm AM/PM"
'Else Leave it as AM, 12 will be PM
     Case 8 To 12
      Target = Target & ":"
      Target.NumberFormat = "h:mm AM/PM"
     End Select
   End If
'Reenable events
  Application.EnableEvents = True
End Sub

If the code fails to complete (like while your testing/changing it) run this little sub to ReEnable events:

Sub ResetEvents()
 Application.EnableEvents = True
End Sub

Report •

June 7, 2010 at 21:08:26
Thank you

Report •

Ask Question