date and time formats

  • Thread starter Thread starter SOPH
  • Start date Start date
S

SOPH

Hi
I am sure this is really simple but it is driving me
insane. I have date data in the format 20040813 and need
to convert it to the format: 13/08/2004 . I also have
time as 0215 and need it to read 02:15. I have a feeling
this is concatenate but when I try I think I am making
this too complicated?
If anyone could help it would be greatly appreciated!!
Cheers
Soph
 
To convert the number 20040813 do data>text to columns, click next twice,
under column data format select date and YMD from the dropdown and click
finish
to convert 215 to 2:15 use a formula

=TIME(INT(A1/100),MOD(A1,100),0)

then copy all the help formulas and paste special as values in place
now you can delete the original value

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
One way ..
.. I have date data in the format 20040813 and need
to convert it to the format: 13/08/2004 .
Click to expand...

In A1: 20040813

Click Data > Text to Columns
Click Next > Next
In step 3 of the wizard,
select "Date" > YMD (from the drop menu)
Click Finish

Now you can format A1 in the desired date format
I also have time as 0215 and need it to read 02:15.
Click to expand...

In A1: 0215

Try in B1:

=TEXT((LEFT(TEXT(A2,"0000"),2)&":"&RIGHT(TEXT(A2,"0000"),2))+0,"hh:mm")

Copy down
 
Hi
I am sure this is really simple but it is driving me
insane. I have date data in the format 20040813 and need
to convert it to the format: 13/08/2004 . I also have
time as 0215 and need it to read 02:15. I have a feeling
this is concatenate but when I try I think I am making
this too complicated?
If anyone could help it would be greatly appreciated!!
Cheers
Soph
Click to expand...

To convert the date:


=DATE(INT(A1/10^4),INT(MOD(A1/10^4,1)*100),MOD(A1/100,1)*100)

and format the result as dd/mm/yyyy

or

=TEXT(DATE(INT(A1/10^4),INT(MOD(A1/10^4,1)*100),MOD(A1/100,1)*100),"dd/mm/yyyy")

=================================

for the time:

=TIME(INT(A1/100),MOD(A1/100,2)*100,0)

and format as h:mm

or

=TEXT(TIME(INT(A1/100),MOD(A1/100,2)*100,0),"h:mm")



--ron
 
If this is something you do regularly then you may want to use a macro so you
can just select the data that you need to convert and then run it in situ:-

Sub ConvertDates()

On Error Resume Next
Application.ScreenUpdating = False

For Each cell In Selection
x = cell.Value
cell.Value = DateValue("01/" & Right(x, 2) & "/" & Left(x, 4))
Next cell

Selection.NumberFormat = "dd-mm-yyyy"
ActiveCell.Select
Application.ScreenUpdating = True

End Sub
 
Hi
Sorry, i am a bit dense, maybe I am not adding this in
correctly, but it keeps giving me 12:00pm no matter what?
What have i done?
Cheers
Soph
 
Sorry, corrected the formula to point to A1 below
In A1: 0215
Try in B1:
Click to expand...

=TEXT((LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))+0,"hh:mm")

The above gives it in text.

You could also put in B1:
=(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))+0
and then custom format B1 in hh:mm format
(via Format > Cells > Custom > In "Type: box:" hh:mm > OK)
 
Soph said:
Hi
Sorry, i am a bit dense, maybe I am not adding this in
correctly, but it keeps giving me 12:00pm no matter what?
Click to expand...

a. Just a thought, as Peo's suggestion works fine for me ..
Maybe your calc mode is indavertently set to Manual?
Try press F9 - Does it now compute?

To check and reset calc mode to "Automatic":
Click Tools > Options > Calculation tab
Check "Automatic" > OK
---
b. Maybe your data in A1 is : 20040813 0215
(i.e. date and time together in one cell)

If so try this:
Select col A
Click Data > Text to Columns (Delimited) > Next

In step 2 of the wizard:
Check "Space" > Next

In step 3 of the wizard, with the first col highlighted

Under "Column data format"
-----------------------------------------
Select "Date" > YMD (choose from the drop menu)

Click Finish

The above will split the date (now in YMD) into col A
and the "time" into col B

Select col A
Click Format > Cells > Custom
In "Type: box:" put "dd/mm/yyyy" > OK
Click to expand...

Now put Peo's formula in C1:
=TIME(INT(B1/100),MOD(B1,100),0)
Click Format > Cells > Custom > In "Type: box:" put "hh:mm" > OK
Copy C1 down
 
Back
Top