Text Date to Date Value

Hi

I'm exporting a .csv file & it's giving text dates in this format: 16/04/2022 (Cell A1)

How to convert this to date value & still keep the same date format - DD/MM/YYYY

Thanks

I'm not sure I understand the question. A csv file is just a text file, and text files can only store text. As far as I know, there is no way to store a format in a text file.

Greg M's code assumes that there is a problem when you import the text file back into Excel, but your question only asks about exporting. If the problem is more about importing the file, then I'm not understanding exactly what problem you are having when you import the file back into Excel (assuming it is even important to import back into Excel -- a lot of these questions that involve exporting a text file are worried about importing the file into another application, so the import process into Excel is not meaningful for the real problem).

Assuming that Greg M's code does not resolve the problem, help us understand exactly what you want stored in the text file, and we should be able to help you export those values to the text file.

MShorty

Yes - Exported data from an application to a .csv file which gives texts

In this instance, all dates shown is a TEXT date - Not an Excel recognized DATE VALUE

Want to convert this into Excel DATE VALUE, keeping the DD/MM/YYYY format

If I recall correctly, there's some type of DATEVALUE function

Greg M
-- Interesting macro - I will also give it a try

Thannks

You can try this:

=DATEVALUE(A1)

or this:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

To add to the other responses, your main concern seems to be the import step, not the export step, is that correct?

As AliGW has shown, there is a DATEVALUE() function that uses your OS's regional setting to decipher text dates into serial numbers. There is also the DATE() function that uses individual numeric (year, month, day) inputs to get to a date serial number. What method are you using to import the text file? If you use the text import wizard (or the Power Query equivalent, if you have a newer version of Excel), you can usually get Excel to correctly interpret the date during the file import step, which means you won't need these functions.

It seems to me that you have control over both the export and import steps. What is the purpose of the text file? If I had control over both export and import, and did not care about needing to see anything in the text file, I would be tempted to export as date serial number (no formatting), then import as date serial number. Less opportunity for Excel to misinterpret a text string.

AliGW

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) works great!!!

Thanks

AliGW

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2) works great!

However, when using filter, the dates are only showing numerical digits
-- See pic

I should be seeing monthly day format

How to make this a date value?

We might need a sample file or a more detailed description of how you got to this point, because I cannot recreate this behavior in my copy of Excel. When I have a date column in Excel and set up autofilter, I get years and months and dates in the filter dropdown for that column.

I do notice that your numbers are close to 5000, which seems small as date serial numbers for dates near 2022 (should be in the 40000s). Are you certain the conversion from text to date worked correctly?

AliGW, MrShorty & Others

See attached example

When filtering in Column Z - Want to see Excel's Date Value or format
-- Using DD/MM/YYYY format

It looks like your date filter is doing the same thing as mine does. Each of those numbers in your screenshot is the "year" number for one or more records in column Z. If you click on the + symbol, you will open up a tree that shows available months for that year. If you click on the + next to the month, you will get a list of available days from that month.

The real curious thing is why you are getting dates in column Z that are 2000 years in the future. As I delved deeper, I discovered that, about halfway through the data set (row 1058), the values in column K became numbers rather than text. K1058 contains the number 44899 (the serial number for 4 Dec 2022). The formula in column Z then splits this number into three different numbers according to the text rules in the formula, so it pulls 4899 for the year number, 99 for the month number (8 years + 3 months, adding to the 4899 year becomes march of 4907), and 44 for the day number (44 days after March 1 4907 is 13 april 4907).

In addition to the shift from text to number, there also appears to be a shift from DMY date interpretation to MDY dates. Above K1058, the dates appear to be going backwards as days in april. Then, at K1058, the dates shift to 4 Dec, then 4 Nov, then 4 Oct, and so on.

If I were fixing this, I would fix this at the data import/entry step -- get Excel to correctly receive the dates in column K, and then column Z is unnecessary and you avoid misinterpreted dates. As I noted above, there are ways (using the Text Import Wizard or whatever Power Query/Get and Transform has replaced it with) to fix this on data import so that Excel will see column K as DMY dates and import them correctly upfront. We haven't seemed interested in this option, but I will mention it again.

It seems that you prefer to fix this after data entry/import. That means that our formula needs an additional IF() test (is column K number or text) =IF(K1058="","",IF(ISTEXT(K1058),currentdateconversionformula,alternatedateconversionformula)). If number, then we will I guess need to assume that Excel misread the import (since we have no way of accessing the original date text string) and switch the month and year values for the date using the YEAR(), MONTH() and DAY() functions. =IF(K1058="","",IF(ISTEXT(K1058),DATE(text functions for each argument),DATE(YEAR(K1058),DAY(K1058),MONTH(K1058)).

Your call on how to handle this, but everything appears to be working correctly on Excel's end. We did not have enough information to know that we needed to check for number and text dates in the same field, so our programming was flawed.

MrShorty & others

Wow!!!

Here's the situation

I'm exporting "Logged In" data from each employee from an app we're using
-- Kind of like a time clock system

This application appears to exporting its raw data as text only, even though dates appears to be in standard date format

This application is really intended for construction safety to know who is onsite

Exports: DD/MM/YYYY format -

21/04/2022

This is OK as long as Excel can "read it" as a date, which is not fully doing

What is the solution to get this consistent for all exported dates?

Thanks

even though dates appears to be in standard date format

A slight correction -- it is presented in a standard DMY date format. It should come as no surprise that there are many different "standard" date formats (MDY, DMY, YMD, and others I'm sure).

Solutions kind of vary. Your profile says you are in GA (Georgia USA??)? If this is a reference to Georgia USA, then your computer's operating system is probably set to prefer MDY dates (because Americans, for some reason, prefer MDY dates). If your specific work is more inclined to use DMY dates, changing the settings in your computer's operating system could be one solution. It's kind of a "global" solution, in that it will impact how all of your apps view/display dates/times (not just Excel). If you prefer to work with DMY dates almost exclusively, this might be the best solution.

I don't know how newer versions of Excel (I notice that your profile does not list an Excel version) prefer to import text files. In my older version, it's a relatively simple Data -> Import Data -> From Text -> Select file -> Text Import wizard comes up and, at step 3 of the wizard, I can specify data types for each field -- including specifying DMY dates for any date fields that use DMY dates. If you can find a "import text file" type of command that will bring up the text import wizard, I can probably help with that.

I don't do PQ, so someone else will need to provide specific instructions there, but I know that PQ is a very useful tool for importing text files into Excel, and I'm almost certain PQ knows how to distinguish between MDY and DMY dates when properly instructed to do so.

Czeslaw

Not sure what you are showing here or how to use

Please clarify

Thanks

Hi Group

I'm still having issues with converting TEXT DATES to Excel's DATE VALUES

To refresh, I'm exporting a .csv vile out of an application that is used for onsite check-ins

When exporting, I'm picking the duration between January 3rd to March 30th, 2022 (Current Date) resulting with:

29/05/2022
29/05/2022
29/05/2022
29/05/2022
29/05/2022
29/05/2022
29/05/2022
29/05/2022
28/05/2022
28/05/2022
28/05/2022
28/05/2022
28/05/2022

== AND ==

14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022

Also, showing dates that ahead of current date


Want to (easily) convert these TEXT DATES to Excel's DATE VALUES with MM/DD/YYYY format

I attached a file for reference


Thanks

These values are not dates, but text. You can use Text to Columns on the Data ribbon to convery them:

1. Select column C.
2. Data ribbon | Text to Columns.
3. Click Next > Next > choose MDY > Finish.

To clarify -- you have a .csv (text) file that is coming from another app. This app is storing the dates in the text file as DMY type dates (dd/mm/yyyy), but your operating system (being set to a North American regional setting) can only recognize MDY dates. When you import the file into Excel (you don't specify exactly how you are importing the file), Excel cannot recognize the file's DMY dates, so Excel stores them as text strings. Am I understanding correctly so far?

As I said above, one of the easiest ways is to somehow get the data to go through the text import wizard where you can specify DMY dates without relying on your operating system's settings. Starting from the text data already imported, this is probably easiest using the text to columns command: https://support.microsoft.com/en-us/...a-7a3e9c363ed7 At step 3 (unless something has dramatically changed), you should be able to specify date format for the date column as DMY and Excel should be able to recognize the dd/mm/yyyy text as dates.

Hi AliGW & MrShorty MrShorty

Thank you both for the quick feedback!

Yes.... This data is being exported out of an app or more specifically called, Sine Pro

I did as instructed & format still appears to be the same

More importantly - At least for April & May - I should have nearly the whole month of work dates
-- Monday through Saturday's for each week of the month
-- This is the time we really start reviewing this application & attempting to create dashboards to determine daily to weekly or monthly onsite workers


Something here is still not working especially, when still results in this format and Excel still reading it as TEXT dates

14/05/2022
14/05/2022
14/05/2022
14/05/2022
14/05/2022

What needs needs to be done to make these TEXT dates into Excel's DATE VALUES?
-- Please use example file given

Thanks

I'm at a loss. Text to columns is one of the most reliable and easiest methods of converting text to numbers. I can only believe that there is something you are not telling us or that your examples are not including. I had no trouble converting your text to dates using text to columns, so I cannot explain why your experience is different. Can you help us understand exactly what you are doing so we can compare your exact steps with what we would expect?

MrShorty & Others

It appears we're both at a loss....

I'll try to break it down more

We're using an app called SINE PRO
-- It's intended to track who is onsite when entering a construction site
-- It also specifies individuals and the company and teams they work for - We have several of them

For Example:

Me: MyCon - Company: ABC - Team: Project Manager
-- I log in everyday - Monday through Friday during the months of April & May
-- Therefore, I should be seeing the dates (and time) I logged in to state I'm on (construction) site


According to this exported .csv. spreadsheet - I only logged in or onsite April 1st through 5th


Unless, I'm doing something wrong - TEXT to COLUMN is giving me same results - TEXT value crap!!!

Using this suggestion AliGW gave kind of worked but DID NOT work 100%

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)

I did report these issues to the SINE PRO admins & they're looking into it

I was hoping for a much better solutions because I'm tracking 100s per day when onsite

At the moment, the only solution that seems to work is manually changing TEXT DATE to Excel DATE VALUE, then copying down the 100s of names

This is OK if tracking per day or week, but once getting into months becomes highly time consuming
-- I need to back track or look at previous months to get historical manpower trends for trend analysis in which I will conduct later

Thanks

Using this suggestion AliGW gave kind of worked but DID NOT work 100%

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)

Can you share examples of when this did not work? With text that is consistently (universally) dd/mm/yyyy, this should have worked just fine. The only reason I can see that this would not work is if your text is sometimes in a different format or has additional characters (maybe hidden/non-printing characters) embedded in the text or if the date has already been converted to number (as some of the examples in the OP were already numbers).

All,

Re-uploaded file inserting

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)

and

Text to Column

Now I feel like we are going in circles. You didn't highlight anything, but this examples looks like the same problem I addressed in post #12 -- when Excel sometimes interprets dates as text and sometimes as numbers (probably because your system is set to MDY, so it will automatically recognize and convert dates that can be interpreted as dates to numbers, mostly the wrong date). Back then, I suggested fixing this by importing the file in a way that does better at correctly interpreting the dates, but did not get much feedback from you on whether or not you were able to implement any of those suggestions. Exactly how are you importing the text file? Can you use a different method for importing the text file?

I might also add a new suggestion -- can this be fixed in SINE PRO (obviously this is an excel forum, I don't think we can help in any way with the details of SINE PRO)? I expect that, if you could convince SINE PRO to output the dates as mm/dd/yyyy text strings, your copy of Excel would have no trouble importing the dates as numbers. The root of the problem is the mismatch between your (at least, I'm assuming, you have never said so explicitly) system's default MDY preference and SINE PRO's preference to output DMY dates. If you could get rid of that mismatch (either by changing SINE PRO's preference or your operating system's setting), then this would be a lot easier.

What kind of changes can you make to the way you are doing this?

MrShorty

Yes... I agree - Seem to be going in circles with what should be a relatively simple conversion solution

This does seem to be a major issue with SINE PRO in which I already started to address to its Admins
-- It's great for logging or showing who is on site but when it comes to dashboards or reviewing log in histories outright - SUCKS!

This is where I was hoping take the exported .csv files and make meaningful dashboards
-- However, if Excel cannot read the inputs correctly, it's going to screw needed information

Thanks all for the assistance

As noted, I can't help with SINE PRO (though, if it defaults to DMY dates, I wonder if it was developed for and works better in Europe and other countries outside the US), but Excel can read the data. What issues have you run into when importing the text file through whatever passes for the "Import data from text" command in your version of Excel? In my version, this would be real easy. I would:

1) Find the Import External Data -> From Text command (usually on the Data ribbon).
2) Select the file.
3) Excel will bring up the text import wizard (same as the Text to columns command). Choose appropriate options -- including DMY dates for the appropriate columns at step 3.
4) Finish the text import wizard and Excel automatically brings up a dialog with some data range options (like whether or not to save the query so it is easier to repeat on a new file or if the existing file changes).
5) Finish and the data is imported to Excel (almost always correctly).
6) If I chose to save the query, I can click on the "refresh data" command and the spreadsheet will update with any new information in the file (or will prompt me to choose a new file, depending on the options I chose in step 4).

My understanding is that, in the newest versions of Excel where Power Query is more integrated into Excel, this operation has been moved under Power Query (Get and Transform), but I expect the same basic functionality (and more) is readily available to the user.

I don't know if that helps, but I see no reason why Excel should not be able to read this data correctly.