Want to pull raw data from one sheet and convert it into oth

September 13, 2017 at 12:34:50
Specs: Windows 10
=IF('Insert Raw Data Here'!C2="","",TEXT('Insert Raw Data Here'!C2,"yyyy/mm/dd, hh:mm:ss"""))

This is the statement I'm trying to make. I want this
2017-06-14 01:55:56 +0000
to show up on another sheet in this format:

06-14-2017 01:55:56

Without the +0000

Is this possible?? Someone please help.

message edited by db20


See More: Want to pull raw data from one sheet and convert it into oth

Reply ↓  Report •

#1
September 13, 2017 at 16:36:35
=IF(C2="","",LEFT(C2,LEN(C2)-6)*1)

Format the destination cell as Custom - mm-dd-yyyy hh:mm:ss

The LEFT function will strip off the last 6 characters: "space+0000"

The result will be a text string that looks like "2017-06-14 01:55:56".

Multiplying the text string by 1 (or adding 0, etc.) will convert that Text string to a number: 42900.0805092593

Since Dates and Times are stored as a number within Excel, applying the Custom Date/Time format will convert that number to 06-14-2017 01:55:56.

(The integer portion is the Date, the decimal portion is the Time)

See here for more info on how Excel deals with Dates and Times:

http://www.cpearson.com/excel/datet...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question