• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel date format issue (convert?)

C

chris.db

New Member
I have data arround 10.000 columms and they have different date format as I need.


They have


20101205 230100

20101205 230200


they are general format, bud when I change it to date format I get only ####


I need format like this:


1.1.2011 0:00

1.1.2011 0:08


Can me somebody help how to make it ?


Thx you very much in forward
 
Chris


Welcome to the Chandoo.org Forums


You said 10,000 columns but I assume you mean 10,000 rows


If your data is in Column 1 and it is yyyymmdd hhmmss

in B1 put =DATE(LEFT(A1,4),MID(A1,5,2),DAY(RIGHT(A1,2))+TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)))

Copy down


Now

Select Column B

Copy and Paste Special, Values

You can then format the columns as required Ctrl 1


If your data is in Column 1 and it is yyyyddmm hhmmss

in B1 use

=DATE(LEFT(A1,4),DAY(RIGHT(A1,2),MID(A1,5,2))+TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)))
 
yes sry rows


I tried both but I get this error


The formue you typed contains an error.


what can be wrong ?


If you give me mail, I can sent you send xls to take a look
 
Hi,


Should that be


=DATE(LEFT(A1,4),MID(A1,5,2),DAY(MID(A1,8,2))+TIME(MID(A1,10,2),MID(A1,12,2),RIGHT(A1,2)))


or


=DATE(LEFT(A1,4),MID(A1,8,2),DAY(MID(A1,5,2))+TIME(MID(A1,10,2),MID(A1,12,2),RIGHT(A1,2)))
 
I don't think I had woken up this morning by the looks of those answers?


If the format is


YYYYMMDD HHMMSS

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,10,2),MID(A1,12,2),RIGHT(A1,2))

or

YYYYDDMM HHMMSS

=DATE(LEFT(A1,4),MID(A1,7,2),MID(A1,5,2))+TIME(MID(A1,10,2),MID(A1,12,2),RIGHT(A1,2))


will do the trick
 
guys :(. thats not working , I have no idea what can be wrong


here is example, please help


thx in forward


http://uploading.com/files/695fe789/date_issue.xlsx/
 
Hi,


Hui's formulas do work, all you need to do is to make the column wider so that you are not seeing ########.


Put your cursor between the column headings B:C until it changes to a double arrow, then just double-click and the column will automatically widen to fit.


Alternatively select heading in column B, right click and pick - Column width, then make it 15, that should be enough.


Custom format column B to:


dd.mm.yyyy hh:mm
 
Hi oldchippy;


I tried previously when I start this topic, but didn`t help..


When is all ok, than is problem with my excel..


I get this error, does anybody whats the issue ?


http://imageshack.us/photo/my-images/20/xlsy.jpg/
 
Chris

The format of the numbers in the file you uploaded is nothing like what you posted above?
 
Is this the correct formula based on your spreadsheet?


=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))


Edit: Again with the custom format
 
Chris

Firstly the numbers in your file are not the same as that posted above


Your File

20110310030100

20110310030900


First Post

20101205 230100

20101205 230200


This has caused a number of us to waste time which is a rare commidity these days.


The numbers in the file you posted were displayed differently using different formats but were just numbers below the surface.


Chippys answer above is correct

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))


If Chippy's formula isn't working for you, let us know what the error message is.


What language version of Excel are you using? as some languages use different commands and some don't use ,s etc
 
sry that I waste your time with my stupid question, but I need to solve this asap


sry one more for the missmatch with xlss,


so again:


I have en excel 2007 (I tried on 2003 and the same)

. I have as separator ( tried , but same... )


when I cope there this formula =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))


I get this error


http://imageshack.us/photo/my-images/219/issue.jpg/


thx
 
Cris

The formula above is working fine in B1 of the file you uploaded before

in both Excel 2007/10


Can you try the whole formula

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

But retype all the (),+ characters manually, there may be a problem with copying/translation


Can you try sections of the formula

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))

and

=TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

each in B1, One at a time


Please report back.
 
thx guys for your time


I was little bit playing with yours formulas of function and investigating


and the right formula is:


=DATE(LEFT(A1;4); MID(A1;5;2); MID(A1;7;2))+TIME(MID(A1;9;2); MID(A1;11;2);MID(A1;13;2))


only to rewrite "," to ";"


Hui, oldchippy thank you so much :)
 
You must log in or register to reply here.
Back
Top