Time and Date Format

  • Thread starter Thread starter Té
  • Start date Start date
T

Té

Everytime I enter 442008, which is supposed to be 4/4/2008, and I go to
format cells and try to convert it to a date format i get 2/6/1912. Why is
the computer giving me a different date? I would like to be able to enter
442008, and Excel automatically converts it to a date. The same thing
happens with the time when I type 8:25 and convert it to time I get 6:00.
Can you please tell me what I am doing wrong? Thank you!!!
 
go to Tools | Options | Transition | tick the microsoft excel and not
lotus notes

This might work out..........
 
In another cell enter:

=DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2,1))

NOTE this will only work with one digit months and days.

1112008 can be either January 11 or November 1
 
I have an entire worksheet full of numbers I want to convert into dates
without deleting everything and type mm/dd/yyyy. Is there a format I can
create?
 
Select the entire worksheet

Go to Ctrl + 1 | number tab | click category custom | type mm/dd/yyyy
| ok
 
that converts them into date format, but it changes the entire date to
something else.
 
If you already have the data in the worksheet and want to convert the numbers
into dates, in place, then Select the cells and run:

Sub dateconverter()
For Each r In Selection
v = r.Text
If IsNumeric(v) Then
l = Len(v)
If l = 8 Then
r.NumberFormat = "m/d/yyyy;@"
r.Value = DateSerial(Right(v, 4), Left(v, 2), Mid(v, 3, 2))
End If
If l = 6 Then
r.NumberFormat = "m/d/yyyy;@"
r.Value = DateSerial(Right(v, 4), Left(v, 1), Mid(v, 2, 1))
End If
End If
Next
End Sub
 
Back
Top