VBA script to double clink on all cells with value in it

[Kerda]

Hi, I have exported a spreadsheet with a mixture of data. Some of the cells contain a date and time in the format of 08/08/2023 17:05. When I double-click on them. They are automatically converted to 08/08/2023 17:05:00 and displayed as 08/08/2023 17:05 but in different font and alignment.

Is it possible to create a script to double-click on all cells in the active spreadsheet to get them all into the same format?

I tried to use cell formatting, and that won't work.

Thank you for your help

 

[Bosquedeguate]

You could write a macro to ensure that all desired cells have the same format.
Or select the columns and set the format (you stated that cell formatting won't work ... why?)

 

[Kerda]

Hi,

Yes, I thought it would be a simple formatting issue. When you click on the cell for the first time, the formatting says "general". When you click away, Excel changes the format to "custom" automatically and the cell looks differently from the rest, as per the example.

But when I try to change the format for multiple cells, they don't change only after I click on them to activate the cell and click away the format changes. Hence, I thought I would need a VBA script to run to activate all the cells in the spread sheet.

 

[Kerda]

03/07/2023 05:57		03/07/2023 14:17
04/07/2023 05:52		04/07/2023 14:12
05/07/2023 05:48		05/07/2023 14:22
06/07/2023 05:53		06/07/2023 13:18
07/07/2023 05:49		07/07/2023 14:05
10/07/2023 13:47		10/07/2023 19:09
11/07/2023 13:32		11/07/2023 19:15
12/07/2023 13:43		12/07/2023 18:56​

 

[Kerda]

found the solution

Converting Text Dates into Numeric Dates​

To convert text dates into numeric values, follow these steps:

1. Start by selecting the dates in your worksheet
2. Click on the Data tab on Excel’s ribbon
3. Click on the Text to Columns button in the Data Tools group
4. In the first step of the Convert Text to Columns Wizard, ensure Delimited is selected (this is the default) and then click Next
5. In the second step, untick any delimiter options that are currently ticked and then click Next
6. In the third step, select Date as your data format
7. In the date drop-down select the format that your dates are currently in eg, DMY
8. Click on Finish to complete the wizard

– In House Excel Training & MS Office Blog

In House Excel Training & MS Office Blog

www.bluepecantraining.com