Time and Date Format

[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!!!

 

[muddan madhu]

go to Tools | Options | Transition | tick the microsoft excel and not
lotus notes

This might work out..........

 

[Gary''s Student]

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

 

[Té]

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?

 

[muddan madhu]

Select the entire worksheet

Go to Ctrl + 1 | number tab | click category custom | type mm/dd/yyyy
| ok

 

[Té]

that converts them into date format, but it changes the entire date to
something else.

 

[Gord Dibben]

Excel will not recognize 442008 as a date.

Or 8:25 as a time.

Enter as 4/4/2008 or 0:8:25

If you don't want to type the / or : then you will need VBA event code.

See Chip Pearson's site.

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord Dibben MS Excel MVP

 

[Gary''s Student]

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

 

[Té]

Thank you so much!!!