Excel - Copy the displayed value not the actual value

Question

I am a pilot, and use a logbook program called Logten Pro. I have the ability to take excel spreadsheets saved from my work flight management software, and import them into Logten Pro using the CSV format.

My problem however, is that the work flight management software, exports the date and time of take-off of a flight into one cell in the following excel format: DD/MM/YYYY H:MM:SS PM. This is handled fine by Excel, and is formatted by default to DD/MM/YY even though the actual value is more specific, comprising of the full length date and time group.

This is a problem because Logten Pro will only auto-import the date if it is in DD/MM/YY format, and there is no way to pull out just the displayed DD/MM/YY date rather than the full date time group actual value, unless you manually go through and delete the extra text from the function box.

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 does, to just the DD/MM/YY value? Additionally, can this be made to work down a whole column rather than individual cells at a time?

Note I have no VBA experience so the perfect answer would just be a complete VBA string I could copy and paste.

Thank You.

Answer 1

As pointed out in the comments, you'd better not use VBA but formulas instead.

This formula:

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

will return the formated date in a text way. You can drag and drop the formula in the whole range of your cells and Copy/Paste Special > Values so that you will only have the needed values to get imported in Logten Pro.

Answer 2

There are three options using formulas.

Rounddown

Excel stores the date time as a number and uses formatting to display it as a date. The format is date.time, where the 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 the values after the decimal place relate to the hours and minutes

So a formula could be used to round the number down to a whole number.

=ROUNDDOWN(A1,0)

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

INT

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

=INT(A1)

Text

Alternately the date only could be extracted as text using this formula

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

It will then display as 01/01/2012