• 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.

Seperating Time and dates from single comum and adjusting time

M

MechEng

New Member
Hi chandooers,


I have what I'm sure is a simple problem but I've no idea how to solve it.

I have a coumn of dates & times and I want to seperate them so the dates are in one column and the times are in the next column.


Also the times are all 8 hours fast so they need to be adjusted, how do I do this?


here is a screenshot of what I'm talkin about


http://i1101.photobucket.com/albums/g434/deco_r/SAMPLE.jpg


and a sample of the actual data


https://rapidshare.com/files/3173470826/SAMPLE.xlsx


the actual data is 1000's of rows long so manualy reformating the data is not an option.


Really appreciate any help on this one.


Thanks :)
 
Mecheng


If that is your data it is not Dates and Times but is Text

Which is good for you


Select the data

Goto Data, Text to Columns

Fixed Width

Excel will choose the gap automatically, Next

In the Data Preview window select the First Column (it will go black)

Change the Column Data Format to Date, MDY

Select the second Column

General

Finish
 
Thanks a million Hui, never would have figured that out myself!:)


What about the times being wrong, is there a quick way to add 8 hours to all of them as they are 8 hours slow.


Thanks again!
 
Hi MechEng,


In the column next to time data you can use this formula =HOUR(G2)+8 and drag down? Then change the format to hh:mm in custom formatting ?

Hope this solves.


Cheers,

Z.
 
Thanks mrzoogle,


unfortunately I couldn't get that to work, it just returns 00:00 for each cell. Any ideas? I tried =HOUR(G2)+8 , =HOUR(B2)+8, =HOUR(B2+8), and just =B2+8 none of them worked.


thanks in advance for any help with this. :)
 
Hi, MechEng!


The values in column A are text values, even if cells are formatted as "hh:mm".


In column B type this:

=FECHANUMERO(IZQUIERDA(A2;8)) -----> in english: =DATEVALUE(LEFT(A2,8))


In column C type this:

=HORANUMERO(DERECHA(A2;5)) -----> in english: =TIMEVALUE(RIGHT(A2,5))


Regards!
 
Hi MachEng,


With due respect to what Hui said (and that worked for me too initially) i would suggest you not to convert text to column. I do't know the reason but with time obtained this way, things are not not working. Instead you may use this formula:


Code: 
=CONCATENATE(LEFT(RIGHT(A2,5),2)+8,":",(RIGHT(A2,2)))


Results:

[pre]02-12-11 21:03	29:03
02-12-11 21:05	29:05
02-12-11 21:06	29:06
02-12-11 21:06	29:06
02-12-11 21:06	29:06
[/pre]
Hope this helps!


Regards,

Faseeh
 
Mecheng


Using your original data


Date

In B2: =DATE(2000+MID(A2,8,2),LEFT(A2,3),MID(A2,5,2))


Time

In C2: =TIME(MID(A2,11,2)+ 8,RIGHT(A2,2),0)

Note the extra 8 Hours added to the time
 
Thanks so much for your help guys!


SirJB7 that changed the date from 2011 to 2001 and did not change the time.


Faseeh that works but 29:03 is not a time, they are 24 hour clock times so it would need to convert 21:00 to 05:00.


Hui, That almost worked perfectly but it changes 02-12-11 21:05 to 12/02/2011 05:05 AM

Thats my fault I should have stated I'm Irish so my dates are in the format dd/mm/yyyy

so the 1st dat is 2nd December 2011 and the date outputed by your formula is 12th February 2011. Also 02-12-11 21:05 plus 8 hours is 03-12-11 05:05 as it ticks over to the next day. Is it possible to get excel to do this?


Thanks again for all the help! :)
 
Change B2 to: '=DATE(2000+MID(A2,8,2),MID(A2,5,2),LEFT(A2,3)+IF(VALUE(MID(A2,11,2))>=16,1,0))`

Copy down


I'm from Australia where we also use dd/mm/yyyy, This is one area where the US has a lot to answer for!
 
Hi, MechEng!


First of all, I apologize for having missed reading the 8 hours time zone difference. Maybe I was in my 8 hours sleeping time.


Second, I assumed you used mm/dd/yyyy as it's the most frequently used here, even if I too use dd/mm/yyyy as I'm argentine. And I agree with Hui about US, but with units in general.


Third, better later than never, here are the right formulas:


B:

=FECHA(VALOR(EXTRAE(A2;8;2))+100;VALOR(EXTRAE(A2;5;2));VALOR(EXTRAE(A2;2;2)))-SI(HORANUMERO(DERECHA(A2;5))<HORANUMERO("08:00:00");1;0) -----> in english: =DATE(VALUE(MID(A2,8,2))+100,VALUE(MID(A2,5,2)),VALUE(MID(A2,2,2)))-IF(TIMEVALUE(RIGHT(A2,5))<TIMEVALUE("08:00:00"),1,0)


C:

=HORANUMERO(DERECHA(A2;5))-HORANUMERO("08:00:00")+SI(HORANUMERO(DERECHA(A2;5))<HORANUMERO("08:00:00");1;0) -----> in english: =TIMEVALUE(RIGHT(A2,5))-TIMEVALUE("08:00:00")+IF(TIMEVALUE(RIGHT(A2,5))<TIMEVALUE("08:00:00"),1,0)


Regards!


PS: BTW, fan of The Chieftains :)
 
Hi MechEng,


I thought you are working with like some sought of observation, from zero hour to onward want to add 8 in that observation. :p


Regards,


@SirJB7: So many MID()s! :D


Regards,

Faseeh
 
@Montrey

Hi!

That was the least I should do after reading so badly...

Regards!


@Faseeh

Hi!

Shorten them. Montrey bets you a buck that cannot.

Regards!
 
@ SirJB7


For Date:
Code: 
=TEXT(LEFT(A2,9),"DD-MM-YYY")

....approx 4.2 times shorter then ur formula.


For Time: =TEXT(RIGHT(A2,5)+TIME(8,0,0),"hh:MM AM/PM")

....approx 2.5 times shorter then urs ;) and I think even shorter then Hui's second formula for B2..


@ Montray


..am waiting for that buck! :)


Regards,

Faseeh
 
@Faseeh

Hi!

You're right, but otherwise how did I was going to earn more money if being paid 0,15€/char?

Regards!

PS: Don't forget my 15% over your new buck!
 
You must log in or register to reply here.
Back
Top