Excel Macro Help

[Ridealong]

Can anyone please?

I need to reformat cells from 2023/11/01T08:00 to 2023-11-01T08:00.
Format Painter doesn't seem to work, nor does Custom Format Cell, I suspect it's the letter T in the middle of the date and time format that stops it from happening.
I create a simply Macro by just going into the cell and deleting the the forward slash and put in the dash. The problems I encounter is (1) After doing the Macro it stay in the same cell/go back to the first cell I altered. (2) I try to add some additional instructions (one click job) for the macro to be copied to a range of cells, but it just copies the first cells data and not the format.

I need help either with changing the whole range of cells or making the Macro go to the next cell down, there should be a simple answer but I can't find it.

Thanks

[simon_harris]

it might be easier to use power query for what you are trying to do, I find it far more useful when doing conversions than macros or formulae.

[Fore Left]

Assuming all fields are the same length this will put the correct format into another cell but won't make Excel see it as a date

=CONCATENATE(LEFT(A1,4),"-",MID(A1,6,2),"-",MID(A1,9,2),"T",MID(A1,12,5))

[JimJobs81]

Can you press ctrl+h to bring up the find and replace box. And then replace / with - ?

[Ridealong]

simon_harris said:

it might be easier to use power query for what you are trying to do, I find it far more useful when doing conversions than macros or formulae.

Sorry I never used Power Query before, I had a look at some examples of what it can do, but doesn't mentioned my problem.

Edited by Ridealong on Monday 4th December 11:50

[bigandclever]

JimJobs81 said:

Can you press ctrl+h to bring up the find and replace box. And then replace / with - ?

That's what I'd do too.

[Mr Pointy]

Post up a picture of the current cell format

[Ridealong]

bigandclever said:

JimJobs81 said:

Can you press ctrl+h to bring up the find and replace box. And then replace / with - ?

That's what I'd do too.

Thank you - I thought that the Find and Replace only worked on letters/numbers and not other characters or symbols.

[Fore Left]

JimJobs81 said:

Can you press ctrl+h to bring up the find and replace box. And then replace / with - ?

So fking obvious when you think about it

OP, just make sure you select the range of cells you want ot do it to first, then you can Replace All

[JimJobs81]

Fore Left said:

So fking obvious when you think about it

I have sent him my Big 4 consulting fee invoice for £20,000+vat!

[Mr Pointy]

JimJobs81 said:

Can you press ctrl+h to bring up the find and replace box. And then replace / with - ?

How will that change the cell format?

[JimJobs81]

Mr Pointy said:

How will that change the cell format?

It doesn't need to. The cell format was text and remains as text. There is no date format with a T in it.

If you want to do date calculations/sorting then you need to convert to a valid date format.

[Mr Pointy]

JimJobs81 said:

Mr Pointy said:

How will that change the cell format?

It doesn't need to. The cell format was text and remains as text. There is no date format with a T in it.

If you want to do date calculations/sorting then you need to convert to a valid date format.

[Fore Left]

Mr Pointy said:

That only works if you input the date/time in the normal format i.e. dd/mm/yyyy hh:mm. It doesn't work if you change the format of cells that already have data in them in the format yyyy-mm-ddThh:mm.

[Mr Pointy]

Fore Left said:

Mr Pointy said:

That only works if you input the date/time in the normal format i.e. dd/mm/yyyy hh:mm. It doesn't work if you change the format of cells that already have data in them in the format yyyy-mm-ddThh:mm.

Not in my spreadsheet. I can enter 2023/11/2 or 2023-11-12 or 12/11/2023 or 12-11-2013 & it will display the same. Of course if cells already have data it doesn't change - it never does in Excel.

[bigandclever]

Mr Pointy said:

Of course if cells already have data it doesn't change.

Isn't that the OP's problem? He already has data that isn't in the right format (nor in a date format).

[Fore Left]

bigandclever said:

Mr Pointy said:

Of course if cells already have data it doesn't change.

Isn't that the OP's problem? He already has data that isn't in the right format (nor in a date format).

This was my point. It only works for new data. I only ever enter dates in the traditional format

[JimJobs81]

Mr Pointy said:

Thanks. You learn something every day!

Do you know why the T is in there? Is it a specific industry that uses that convention?

[paulrockliffe]

Ridealong said:

Sorry I never used Power Query before, I had a look at some examples of what it can do, but doesn't mentioned my problem.

Edited by Ridealong on Monday 4th December 11:50

It can do pretty much anything and it's pretty easy to pick up and just use the GUI to find an answer to your issue. It is macros on steroids for organised data.

Your issue you simply select the column that's wrong, replace the T with a colon, then format the column as DateTime or Date, whatever you need. Then any new data will get the same transformations applied to it.

What you lose is the immediacy and instant calculation that happens in an Excel Workbook; the result from Power Query you load into a Pivot Table or Chart and when you add new data you need to Refresh those to pull the new, transformed, data through.

Unless you're modelling something across cells and formulas, Power Query is where I would always start. It means you can separate the data collection thing from the data presentation thing with what is essentially a powerful and easy to use enterprise-grade ETL process in the middle. When you put the results into Power Pivot and create relationships between tables, you have a very powerful database inside Excel.

[Mr Pointy]

JimJobs81 said:

Mr Pointy said:

Thanks. You learn something every day!

Do you know why the T is in there? Is it a specific industry that uses that convention?

I don't know, it was in the format that OP was after.