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

Extract the time and date from a data extraction

T

thesilkster

Member
Hello I have an extraction from a system and am trying to put a date and time in the column to the left looked at loads of info on the web but cant find anything relating to the TRAN_TIME format?

Any help is appreciated I have added the spreadsheet
upload_2018-11-28_15-31-10.png
 

Attachments

  • Time Stamp.xlsx
    8.5 KB · Views: 7
Hi,

Based on your data structure, if your date is YYYYMMDD and time is HHMMSS, this can be used:

=DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))+TIME(LEFT(E2,2),MID(E2,3,2),RIGHT(E2,2))

Regards,
 
Khalid NGO said:
Hi,

Based on your data structure, if your date is YYYYMMDD and time is HHMMSS, this can be used:

=DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))+TIME(LEFT(E2,2),MID(E2,3,2),RIGHT(E2,2))

Regards,
Click to expand...
Thanks for the rely but the trans time is digital I think so 105514 is not 10:55:14
 
If you don't think it's HHMMSS format, then check with your vendor/db admin.

Though I highly doubt that it's in format other than, HHMMSS.
It's typical for db admin (or software) to generate report using YYYYMMDD, HHMMSS for dates and times, and let end user deal with final formatting according to their system.
 
Similar assumptions to the above but I have stayed with numerical rather than text processing, since your date and time inputs are numbers. To make the input range dynamic, as well as to introduce structured references, I would first convert the data to a Table [Ctrl+L]. The formulas for hours, minutes and seconds would be

= FLOOR(Table1[@[TRAN_TIME]],10000)/10000
= MOD(FLOOR(Table1[@[TRAN_TIME]],100)/100, 100)
= MOD(Table1[@[TRAN_TIME]],100)

Because such formulas are ugly and not too informative, I define Names 'hr', 'mm' and 'ss' to refer to the formulas, so that all that appear on the worksheet is

= TIME( hr, mm, ss )

The result is a proper time value that can be used for further calculation within Excel. If you also convert the date, the time can be added to the date to give the combined date/time.
 

Attachments

  • Time Stamp (PB).xlsx
    10.3 KB · Views: 12
The difference is that your original post had 6-digit strings for the time whereas the image shows 8 digits. The attached addresses the new format but if it is variable a little bit of fresh thinking would be required.
 

Attachments

  • Time Stamp (PB).xlsx
    10.9 KB · Views: 7
Just incase anyone is following this discussion but does not choose to open workbooks, the formulae I have used are:

'TT' is defined to be the first 4 digits of TRAN_TIME
= FLOOR( Table1[@[TRAN_TIME]], 10000 ) / 10000
Hours, 'hr', is taken from the first 2 digits of TT
= FLOOR( TT, 100 ) / 100
Minutes, 'mm' are the final 2 digits of TT
= MOD( TT, 100 )
The time is given by
= TIME( hr, mm, 0 )
I have ignored seconds.
 
Last edited:
You must log in or register to reply here.
Back
Top