Excel - copying the displayed value is not the actual value

Question

I am a pilot and use the Logten Pro registration program. I have the opportunity to use Excel spreadsheets saved from my flight control software and import them into Logten Pro using CSV format.

However, my problem is that the flight management software exports the date and time of take-off in one cell in the following Excel format: DD / MM / YYYY H: MM: SS PM. This is handled perfectly by Excel, and by default it is formatted in DD / MM / YY format, even if the actual value is more specific, consisting of a full-length date and time group.

This is a problem because Logten Pro will automatically import the date if it is in DD / MM / YY format, and there is no way to pull out only the displayed DD / MM / YY date, and not the full date, the actual time group value, unless you pass manually and do not remove additional text from the function block.

My question is: is there a VBA macro that can automatically copy the actual displayed text and paste it into another cell, changing the actual value, as it happens, to the value DD / MM / YY? Also, can this be done to work on an entire column, rather than on individual cells at a time?

Note. I do not have VBA experience, so the only complete line of VBA that I could copy and paste would be the ideal answer.

Thanks.

Answer 1

As stated in the comments, you better not use VBA, but formulas.

This formula:

TEXT(A1,"dd-mm-yyy") 

returns a formatted date in a textual manner. You can drag the formula to the entire range of your cells and Copy/Paste Special > Values , so that you only have the necessary values ​​to import into Logten Pro.

Answer 2

There are three options for using formulas.

ROUNDDOWN

Excel stores the date as a number and uses formatting to display it as a date. The format is date.time, where integer is the date, and the fraction is the time.

As an example, 01/01/2012 10:30:00 PM is stored as 40909.9375

All values ​​after the decimal point are hours and minutes.

Thus, the formula can be used to round a number to an integer.

 =ROUNDDOWN(A1,0) 

Then format the value as a short date. It will display as 01/01/2012

INT

As above, but using a different formula to get rid of the fraction (time)

 =INT(A1) 

Text

Alternatively, the date can only be retrieved as text using this formula.

 =TEXT(A1,"dd/mm/yyyy") 

Then it will appear as 01/01/2012