Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert date/time formats differently?
Hi folks. Whenever I pull data from my work dat server, I will get a
date/time data in this yyyymmdd hhmmss format. If the some times I get the date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how do I change the date/time format into something simpler, so that I can calculate the time difference in terms of hh:mm:ss ? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert date/time formats differently?
Hi "Ruidil"
If the data is in date/time format you just need to substract it Say you have FROM date/time in A1 and TO date/time in B1 To substract in C1 enter formula =B1-A1 and custom format that cell to [h]:mm:ss OR Now if your initial data is in text format you need to convert it to time format Suppose you have data in Col A, Cell A1 20090606 132400 In B1 enter formula =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(LEF T(RIGHT("00" & MID(A1,FIND(" ",A1)+1,10),6),2),MID(RIGHT("00" & MID(A1,FIND(" ",A1)+1,10),6),3,2),RIGHT(RIGHT("00" & MID(A1,FIND(" ",A1)+1,10),6),2)) and format B1 to which ever format you would like Once your data is in date format you can find the difference using above said method and format the cell to [h]:mm:ss to get the total hour difference. If this post helps click Yes --------------- Jacob Skaria "Ruidil" wrote: Hi folks. Whenever I pull data from my work dat server, I will get a date/time data in this yyyymmdd hhmmss format. If the some times I get the date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how do I change the date/time format into something simpler, so that I can calculate the time difference in terms of hh:mm:ss ? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert date/time formats differently?
If you have spaces after the entry like
"20090606 130201 " the earlier formula will return error #value; in which case use the below formula to convert the text cell to date/time format. All in one line =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+ TIME(LEFT(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),2), MID(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),3,2), RIGHT(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),2)) and format it to which ever date/time format as you need. If this post helps click Yes --------------- Jacob Skaria "Ruidil" wrote: Hi folks. Whenever I pull data from my work dat server, I will get a date/time data in this yyyymmdd hhmmss format. If the some times I get the date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how do I change the date/time format into something simpler, so that I can calculate the time difference in terms of hh:mm:ss ? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert date/time formats differently?
Try this:
=TEXT(LEFT(A1,8)&TEXT(MID(A1,10,9),"000000"),"0000-00-00 00\:00\:00")+0 "Ruidil" wrote: Hi folks. Whenever I pull data from my work dat server, I will get a date/time data in this yyyymmdd hhmmss format. If the some times I get the date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how do I change the date/time format into something simpler, so that I can calculate the time difference in terms of hh:mm:ss ? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I convert date/time formats differently?
On Sat, 6 Jun 2009 03:49:01 -0700, Ruidil
wrote: Hi folks. Whenever I pull data from my work dat server, I will get a date/time data in this yyyymmdd hhmmss format. If the some times I get the date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how do I change the date/time format into something simpler, so that I can calculate the time difference in terms of hh:mm:ss ? Thanks. =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+ TEXT(TRIM(MID(A1,FIND(" ",A1),10)),"00\:00\:00") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to easily convert US to UK date formats? | Excel Worksheet Functions | |||
What exactly does the asterisk mean in the Date/Time formats? | Excel Discussion (Misc queries) | |||
formulas using date/time formats returning #value | Excel Discussion (Misc queries) | |||
Multiple Date/Time Formats | Excel Worksheet Functions | |||
US vs UK date and time formats | Excel Discussion (Misc queries) |