Found 12th Jul
I need some help with some date formatting/conversions. Screenshot is below. this is a small snapshot of the whole worksheet, but its the same throughout. Basically I need the whole column(s) to match, in the exact same format, with date and time. So, taking the first 2 rows for example, row 1 shows 14:16:28 on 28th March 2017. Row 2 shows 25th May, 2017, the time being 06:38:47 seconds, but completely different formatting. Any ideas how I do this?

  1. Ask
  2. Microsoft
7 Comments

Original Poster

https://img2.brain3.photobox.com/942548904103d55b7ddd14bfb58c63c606c6b474bc9d5d4b922378ecae007341fecefbe4.jpg

New column =TEXT(A1,"DD/MM/YYYY HH:MM)

If that doesn't work you can try find and replace "{" with "" and "}" with "". Then in another column you can enter =CONCATENATE(A2,"-",B2,"-",C2,"_",D2,":",E2,":",F2).

Edit: You should put the above in the the first even row and =A1 in the first odd row then you should highlight both cells and draw the box to the end of the column.
Edited by: "bigfoot100" 12th Jul

Hmm looks like it would have to be done in 2 parts.

1st filter data set (sheet 1):

-add line in row one and label your column 3: Date, year & month
-Using filter, filter by "}" symbol
- Highlight all and cut and paste onto separate sheet

On the separate sheet (call it sheet 2) with data containing "{" and "}" symbols:

-Press Ctrl+F, select replace:
-in find what field type: "{" the symbol without quotation marks, leave replace with blank
- press replace all
-now redo with the find what field showing: "}" and again leave replace with blank
- press replace all

now to get data formatted:

in column H use formula: =A1&"-"&B1&"-"&C1&"_"&D1&":"&E1&":"&F1
in column I: =A5
in Column J: = B5

Column's H, I & J contain your final formatted data, copy and paste as values back into original worksheet


Original Poster

Thanks all, will give this a go and let you know if it works

Original Poster

https://img2.brain4.photobox.com/5066513674c3d5f5f5c203a056a7c66d9c2b24e5c047cac9ed3ffee5b2e217e307cf75fa.jpg

Ok, Nearly there, I think....got rid of all the brackets, just got to get the output to all be in the correct, matching format in column H. Have I got it very wrong?

mcscoobs

Ok, Nearly there, I think....got rid of all the brackets, just got to get … Ok, Nearly there, I think....got rid of all the brackets, just got to get the output to all be in the correct, matching format in column H. Have I got it very wrong?



Looks close but,
In H2 you need to put =B2
In H3 you need to put =B3&"-"&C3&"-"&D3&"_"&E3&":"&F3&":"&G3
Post a comment
Avatar
@
    Text
    Top Discussions
    1. Computer upgrades89
    2. Invisalign dental braces in London22
    3. Whey protien629
    4. Gearbest: Order - Nowhere to be seen...811

    See more discussions