Problem with Excel Dates

[assertec]

Hi guys,

I receive spreadsheets every month that have dates in column A.

These dates are perfect when they come to me; day in the right place, month, year in the correct place.

Though the data needs to be moved around a lot from worksheet to worksheet, and having doing so, some of the months become days, and some of the days become months.

For example; I receive a date 12/3/2013... that may or may not be converted to 3/12/2013... I may recieve other dates that don't get converted at all and are perfect as they were when the spreadsheet was initially received.

My question is this:

Is there any code that can grab these dates when they first come into our Excel VBA program that can keep the dates stay EXACTLY the same throughout this sheet to sheet movement - as when they came in?

So if they came in like 7/3/2013 it would stay 7/3/2013... if they came in like 3/7/2013 it would stay 3/7/2013... or if they came in like 7-3-2013 it would stay 7-3-2013... if they came in like 3-7-2013 it would stay 3-7-2013.

Thanks in advance.

Karen

 

[GS]

Hi guys,

I receive spreadsheets every month that have dates in column A.

These dates are perfect when they come to me; day in the right place,
month, year in the correct place.

Though the data needs to be moved around a lot from worksheet to
worksheet, and having doing so, some of the months become days, and
some of the days become months.

For example; I receive a date 12/3/2013... that may or may not be
converted to 3/12/2013... I may recieve other dates that don't get
converted at all and are perfect as they were when the spreadsheet
was initially received.

My question is this:

Is there any code that can grab these dates when they first come into
our Excel VBA program that can keep the dates stay EXACTLY the same
throughout this sheet to sheet movement - as when they came in?

So if they came in like 7/3/2013 it would stay 7/3/2013... if they
came in like 3/7/2013 it would stay 3/7/2013... or if they came in
like 7-3-2013 it would stay 7-3-2013... if they came in like 3-7-2013
it would stay 3-7-2013.

Thanks in advance.

Karen

This might be a system settings issue. I've observed that Win7, for
example, uses dd/mm/yyyy format while Vista/XP are mm/dd/yyyy. My
solution was to use a custom date format which either system setting
works with...

"mmm d yy"

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

Hi guys,

I receive spreadsheets every month that have dates in column A.

These dates are perfect when they come to me; day in the right place, month, year in the correct place.

Though the data needs to be moved around a lot from worksheet to worksheet, and having doing so, some of the months become days, and some of the days become months.

For example; I receive a date 12/3/2013... that may or may not be converted to 3/12/2013... I may recieve other dates that don't get converted at all and are perfect as they were when the spreadsheet was initially received.

My question is this:

Is there any code that can grab these dates when they first come into our Excel VBA program that can keep the dates stay EXACTLY the same throughout this sheet to sheet movement - as when they came in?

So if they came in like 7/3/2013 it would stay 7/3/2013... if they came in like 3/7/2013 it would stay 3/7/2013... or if they came in like 7-3-2013 it would stay 7-3-2013... if they came in like 3-7-2013 it would stay 3-7-2013.

Thanks in advance.

Karen

You do not provide sufficient information to provide definite advice.

The kind of problem you describe is seen when importing a .csv file where the format of the dates in the .csv file and the Windows regional settings of your computer are different. The simplest way to deal with this manually, at least in Excel 2007 or later (I can't recall the process in earlier versions), would be to use the "Get External Data From Text" process. This allows you to use the Text Import Wizard which allows specifying the format of the column containing dates to be the same as that of the .csv file. They would then be stored as a real excel date.

This can be automated using VBA.

 

[assertec]

Hi guys,



I receive spreadsheets every month that have dates in column A.



These dates are perfect when they come to me; day in the right place, month, year in the correct place.



Though the data needs to be moved around a lot from worksheet to worksheet, and having doing so, some of the months become days, and some of the days become months.



For example; I receive a date 12/3/2013... that may or may not be converted to 3/12/2013... I may recieve other dates that don't get converted at all and are perfect as they were when the spreadsheet was initially received.



My question is this:



Is there any code that can grab these dates when they first come into ourExcel VBA program that can keep the dates stay EXACTLY the same throughoutthis sheet to sheet movement - as when they came in?



So if they came in like 7/3/2013 it would stay 7/3/2013... if they came in like 3/7/2013 it would stay 3/7/2013... or if they came in like 7-3-2013 it would stay 7-3-2013... if they came in like 3-7-2013 it would stay 3-7-2013.



Thanks in advance.



Karen

Thanks for your replies, though maybe I should clarify.

I get data sent to me each month in a .csv file.

This data has dates in column A.

These dates appear perfect, and are displayed exactly as required for the final reports.

However, this data (including the dates) gets moved around from sheet to sheet, as VBA macros do certain calculations etc in order to get the finishedreports.

So when the dates are finally shown on the finished reports, some of the dates have the month where the day should go, and some have day where the month should go. Some of the dates don't get altered at all... this is becausesome of the dates are actual text dates, so these dates are perfect because they appear on the finished reports exactly as they appear when the .csv file is sent to me.

What I would like to do is fix the dates so none of them get altered.

I've tried converting all the dates to text using the text to columns routine, however even by doing this and running it through the VBA macros, the initial numeric dates still get altered, switching the months and days again..

Think of it this way; the dates come into the VBA macros. I take a photo/screen shot of these dates. I'd like them to appear on the finished reports exactly the same as they did on the photo/screen shot.

Is there a way of like cementing these dates so they remain completely fixed, and do never get altered as they are moving from sheet to sheet. I've also tried placing brackets around them, and I've tried placing asterisks around them - though nothing seems to help.

As I said earlier, the dates appear/display perfect before they go into theVBA macro, and that's how I'd like them to look in the finished reports - exactly as they appear at the start.

Thanks in advance.

Karen

 

[Bruce Sinclair]

t, and having doing so, some of the months become days, and some of the day=
s become months.
ed to 3/12/2013... I may recieve other dates that don't get converted at al=
l and are perfect as they were when the spreadsheet was initially received.
Excel VBA program that can keep the dates stay EXACTLY the same throughout=
this sheet to sheet movement - as when they came in?=20
n like 3/7/2013 it would stay 3/7/2013... or if they came in like 7-3-2013 =
it would stay 7-3-2013... if they came in like 3-7-2013 it would stay 3-7-2=
013.

Thanks for your replies, though maybe I should clarify.

I get data sent to me each month in a .csv file.

This data has dates in column A.

These dates appear perfect, and are displayed exactly as required for the f=
inal reports.=20

However, this data (including the dates) gets moved around from sheet to sh=
eet, as VBA macros do certain calculations etc in order to get the finished=
reports.

So when the dates are finally shown on the finished reports, some of the da=
tes have the month where the day should go, and some have day where the mon=
th should go. Some of the dates don't get altered at all... this is because=
some of the dates are actual text dates, so these dates are perfect becaus=
e they appear on the finished reports exactly as they appear when the .csv =
file is sent to me.

What I would like to do is fix the dates so none of them get altered.=20

I've tried converting all the dates to text using the text to columns routi=
ne, however even by doing this and running it through the VBA macros, the i=
nitial numeric dates still get altered, switching the months and days again=
..

Think of it this way; the dates come into the VBA macros. I take a photo/sc=
reen shot of these dates. I'd like them to appear on the finished reports e=
xactly the same as they did on the photo/screen shot.

Is there a way of like cementing these dates so they remain completely fixe=
d, and do never get altered as they are moving from sheet to sheet. I've al=
so tried placing brackets around them, and I've tried placing asterisks aro=
und them - though nothing seems to help.

As I said earlier, the dates appear/display perfect before they go into the=
VBA macro, and that's how I'd like them to look in the finished reports - =
exactly as they appear at the start.

Thanks in advance.

Sounds like different sheets have different defaults (though I thought that
was a system setting and not settable by workbook). Could also be the VBA
that's changing it (and/or *assuming* things about the formatting). This
could be a "Really Bad Thing" depending on what you are using the data for.

Suggest formatting the dates when you first import them into XL as a textr
field (eg ##-mmm-yyyy) and (hopefully ?) that format at least would carry
over? Then you would at least be able to tell which was what if the order
got changed. .
That assumes of course that all the VBA is doing is moving things around.
I would be worried first about whether or not it was just formatting that
was being changed or if the dates were effectively wrong in some of the
manipulations.

As an aside, SI standard format for dates is a text string: yyyymmdd (and
that can be extended with time as hhmmss). Always interpretable without any
more information (like which format).

HTH

 

[Ron Rosenfeld]

On Tue, 19 Mar 2013 18:51:07 -0700 (PDT), (e-mail address removed) wrote:

Ah. Some more information.

I get data sent to me each month in a .csv file.

These dates appear perfect, and are displayed exactly as required for the final reports.

However, this data (including the dates) gets moved around from sheet to sheet, as VBA macros do certain calculations etc in order to get the finished reports.

So when the dates are finally shown on the finished reports, some of the dates have the month where the day should go, and some have day where the month should go. Some of the dates don't get altered at all... this is because some of the dates are actual text dates, so these dates are perfect because they appear on the finished reports exactly as they appear when the .csv file is sent to me.

What I would like to do is fix the dates so none of them get altered.

I've tried converting all the dates to text using the text to columns routine, however even by doing this and running it through the VBA macros, the initial numeric dates still get altered, switching the months and days again.

As I suspected in my initial post, you DO get the data as a .csv file.
You need to apply the text-to-columns wizard BEFORE the data gets to the worksheet. Once the data gets to the worksheet, Excel has already converted the dates that "make sense" to it given the Windows Regional Settings.;
For the final report, you can merely format the cells appropriately in Excel.

As I wrote previously, with a .csv file, one way to do this is to use the Text Import wizard from the Data ribbon and NOT just OPEN the .csv file in Excel. That will bring up the Text Import wizard BEFORE Excel has converted the dates to "real dates" (which are serial number with 1 usually equal to 1 Jan 1900.

Other options include changing your Windows REgional date settings (accessed via the Windows Control Panel) to the same format as that of the .csv file.
A third option would be to change the file type to a .txt file, which would also bring up the text import wizard BEFORE the data gets to the worksheet.

 

[GS]

I'm just understanding this problem for the first time, now that we
know the data comes from a CSV file directly opened in Excel. I recall
doing this a few times a long time ago (it seems) and getting
unexpected formatting as a result.

I started using VBA to import data from delimited files into an array
and dump the array into the worksheet. Since then I haven't gotten any
unexpected formatting! Seems this approach mimics (perhaps) what the
TextImportWizard does?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

I'm just understanding this problem for the first time, now that we
know the data comes from a CSV file directly opened in Excel. I recall
doing this a few times a long time ago (it seems) and getting
unexpected formatting as a result.

I started using VBA to import data from delimited files into an array
and dump the array into the worksheet. Since then I haven't gotten any
unexpected formatting! Seems this approach mimics (perhaps) what the
TextImportWizard does?

Not really. The Text Import Wizard works the same as the Text to Columns wizard. One needs to specify the date format in the file being imported. The benefit of IMPORT vs just reading it into a worksheet (or a VBA array), is that you have the opportunity to specify the incoming date format BEFORE Excel (or VBA) converts the information into a serial number.

I suppose if you just wanted to treat the dates as text strings, and will NEVER need to treat them as dates, reading them into a VBA 'string' array might preserve the string intact. If that is then placed in a worksheet region that has been pre-formatted as text, it should retain the incoming format. But that is not an approach I would ever recommend because of the risks of messing things up somewhere down the line.

Once some of the dates are translated to serial numbers, it's really too late to use that approach. It should be possible to convert even after import, depending on the format the csv file is using, and the OP's Regional Settings, but it's much simpler to do it correctly the first time, especially since this is a repetitive task.

Given the paucity of detail the OP has provided, it is difficult to make specific recommendations.

 

[GS]

Not really. The Text Import Wizard works the same as the Text to
Columns wizard. One needs to specify the date format in the file
being imported. The benefit of IMPORT vs just reading it into a
worksheet (or a VBA array), is that you have the opportunity to
specify the incoming date format BEFORE Excel (or VBA) converts the
information into a serial number.

I suppose if you just wanted to treat the dates as text strings, and
will NEVER need to treat them as dates, reading them into a VBA
'string' array might preserve the string intact. If that is then
placed in a worksheet region that has been pre-formatted as text, it
should retain the incoming format. But that is not an approach I
would ever recommend because of the risks of messing things up
somewhere down the line.

Given that the primary function of the import is to generate/manipulate
various custom reports via VBA, code handles the date data correctly
quite easily. Outside of that I agree with your recommendation!

Once some of the dates are translated to serial numbers, it's really
too late to use that approach. It should be possible to convert even
after import, depending on the format the csv file is using, and the
OP's Regional Settings, but it's much simpler to do it correctly the
first time, especially since this is a repetitive task.

Given the paucity of detail the OP has provided, it is difficult to
make specific recommendations.

Well.., I agree with you on that...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

Given that the primary function of the import is to generate/manipulate
various custom reports via VBA, code handles the date data correctly
quite easily. Outside of that I agree with your recommendation!

Code can certainly handle it. But still requires knowledge of the date format of the incoming csv file. All the OP has written is that he wants the format to "stay the same". We don't even know if this format is fixed or variable (as it might be if entered manually, or if various csv files come from different locales).

 

[GS]

But still requires knowledge of the

date format of the incoming csv file. All the OP has written is that
he wants the format to "stay the same". We don't even know if this
format is fixed or variable (as it might be if entered manually, or
if various csv files come from different locales).

That would certainly makes things more difficult, Ron! I normally
convert the date string to Date datatype and format it how my app
want it.

Recently, though, I've noticed (in Win7) that whether the dates are
"d/m/y" or "m/d/y" Excel seems to have no problem when dates are used
in formulas. But if I use a file on a Win7 machine and open it in XP
later there's a problem with those same formulas working with dates
entered on the Win7 system, depending on the day number. (23/12/2012
doesn't work in XP expecting 12/23/2012 format) That suggests to me
that Win7 handles dates in a new way over previous OSs, though I doubt
this can be verified.

Strangely, I get LicenseProfile strings with the StartDate and
DateLastUsed imbedded in them from Win7 machines and these seem to sit
fine with XP when parsed into the thier respective data column of my
license key generator workbook. I suspect, though, that Excel is
treating these as a sring since I use the same methodology to
read/parse the file as I do for reading plain text delimited files. (I
pretty much have optimized the process of storing
data/settings/options/configs in plain text files because I don't use
the Registry for anything but retrieving system/environ info!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

Recently, though, I've noticed (in Win7) that whether the dates are
"d/m/y" or "m/d/y" Excel seems to have no problem when dates are used
in formulas. But if I use a file on a Win7 machine and open it in XP
later there's a problem with those same formulas working with dates
entered on the Win7 system, depending on the day number. (23/12/2012
doesn't work in XP expecting 12/23/2012 format) That suggests to me
that Win7 handles dates in a new way over previous OSs, though I doubt
this can be verified

I'm not sure I understand what you are writing. Excel (both pre-2007 and 2007) use the same rules for parsing what appear to be date strings, and it corresponds to the short date format in the Windows Regional Settings (WRS).
If the date is not interpretable based on the WRS, then the data will be interpreted as a text string. e.g. if the WRS is m/d/y and the data is 23/12/2006 (e.g. d/m/y), then Excel will interpret the data as a text string, since a month cannot have a numeric representation greater than twelve. If the data is 5/12/2006, and the WRS the same m/d/y, the date will be interpreted as 12-May-2006. (If the WRS is d/m/y, then Excel would interpret it as 5-Dec-2006). I've not seen any change in this behavior between Excel versions prior to 2007, Excel 2007, W7 or Windows XP.

If the date is expressed unambiguously, e.g. 23-Dec-2012, then Excel will interpret the string as expected, no matter the WRS.

 

[GS]

I'm not sure I understand what you are writing. Excel (both pre-2007
and 2007) use the same rules for parsing what appear to be date
strings, and it corresponds to the short date format in the Windows
Regional Settings (WRS). If the date is not interpretable based on
the WRS, then the data will be interpreted as a text string. e.g. if
the WRS is m/d/y and the data is 23/12/2006 (e.g. d/m/y), then Excel
will interpret the data as a text string, since a month cannot have a
numeric representation greater than twelve. If the data is
5/12/2006, and the WRS the same m/d/y, the date will be interpreted
as 12-May-2006. (If the WRS is d/m/y, then Excel would interpret it
as 5-Dec-2006). I've not seen any change in this behavior between
Excel versions prior to 2007, Excel 2007, W7 or Windows XP.

If the date is expressed unambiguously, e.g. 23-Dec-2012, then Excel
will interpret the string as expected, no matter the WRS.

Yes, I agree with you! What I was trying to say is that when I enter
today's date I always use the keyboard shortcut 'Ctrl+;'. Doing this
when my file is open on my XP machine it displays 3/20/2013. If I open
the same file on a Win7 machine and enter today's date in the same way
it displays 20/3/2013. When I open that file again on my XP machine it
doesn't recognize the date as a date, and so dependant formulas don't
work. What I don't get is that if Excel stores dates as serial nums and
then why wouldn't the formulas work regardless of the system?

As I mentioned earlier I started using a custom format for dates
("Mar-20") and the problem disappeared. It's weird, I'll admit, but I
suspect it might be happening for some other reason that I haven't
pinpointed as yet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[assertec]

Hi guys,



I receive spreadsheets every month that have dates in column A.



These dates are perfect when they come to me; day in the right place, month, year in the correct place.



Though the data needs to be moved around a lot from worksheet to worksheet, and having doing so, some of the months become days, and some of the days become months.



For example; I receive a date 12/3/2013... that may or may not be converted to 3/12/2013... I may recieve other dates that don't get converted at all and are perfect as they were when the spreadsheet was initially received.



My question is this:



Is there any code that can grab these dates when they first come into ourExcel VBA program that can keep the dates stay EXACTLY the same throughoutthis sheet to sheet movement - as when they came in?



So if they came in like 7/3/2013 it would stay 7/3/2013... if they came in like 3/7/2013 it would stay 3/7/2013... or if they came in like 7-3-2013 it would stay 7-3-2013... if they came in like 3-7-2013 it would stay 3-7-2013.



Thanks in advance.



Karen

Thanks again for your replies guys.

Here's further clarification:

Yes the files I receive with these dates is a .csv file.
I receieve a number of these files each month from the same supplier.

The files always have a list of dates going down column A... approx 500 rows in each file.

These dates are not used in any calculations... so they can remain as text throughout the VBA movement routine. They are only important to the resultsonce the VBA code has done its job creating the reports with the other data that is displayed to the right of these dates eg. column B, C, D, E etc.

Here's an example of some of these dates:

2/01/2013
2/01/2013
3/01/2013
3/01/2013
4/01/2013
4/01/2013
7/01/2013
8/01/2013

These dates above would normally appear as:

1/02/2013
1/02/2013
1/03/2013
1/03/2013
1/04/2013
1/04/2013
1/07/2013
1/08/2013

Though also in the list of dates I get these:

17/01/2013
18/01/2013
18/01/2013
21/01/2013
22/01/2013
22/01/2013

These dates remain the same thoughout the VBA routine because of course youcan't have 17,18,21,22 months in a year... so Excel leaves these dates alone. These dates are perfect for the finished report because they are exactly has they should be i.e. unchanged.

So if for example I used a date format that would switch/display the day and month around, that won't work because it would also switch around those that are perfect.

As I said earlier, the dates are not used in any calculations... so having them as a text string would be fine. The dates are only used for display purposes only for the finished reports.

All I need to do is stop these dates from switching the months and days around. Like set them in stone, just as if they were any other label such as Fred, Mary, Harry etc.

I haven't tried importing the file using the Import Wizard (i.e. by creating a .txt file out of the .csv file) because the program with the VBA code expects the file to be a .csv file... the code is written to only accept/open .csv files.

Thanks again in advance.

Karen

 

[Ron Rosenfeld]

Yes, I agree with you! What I was trying to say is that when I enter
today's date I always use the keyboard shortcut 'Ctrl+;'. Doing this
when my file is open on my XP machine it displays 3/20/2013.

That means that the date format in your Excel workbook is m/dd/yyyy

If I open
the same file on a Win7 machine and enter today's date in the same way
it displays 20/3/2013.

That means the date format on that excel workbook is dd/m/yyyy

When I open that file again on my XP machine it
doesn't recognize the date as a date, and so dependant formulas don't
work.

How did you save that file? I can easily reproduce the issue if I save the file as a .csv file. Then it is saved as text and when you open it in a machine that does not have that same WRS, it cannot recognize it. There may be other ways to reproduce the problem also, not sure.

What I don't get is that if Excel stores dates as serial nums and
then why wouldn't the formulas work regardless of the system?

See above.

As I mentioned earlier I started using a custom format for dates
("Mar-20") and the problem disappeared.

That would be because "Mar-20" is an unambiguous representation of the date, so it can easily be interpreted even if it is a text string.

 

[Ron Rosenfeld]

I haven't tried importing the file using the Import Wizard (i.e. by creating a .txt file out of the .csv file) because the program with the VBA code expects the file to be a .csv file... the code is written to only accept/open .csv files.

Then use the other suggestion which did not require you to change the file type.

 

[Ron Rosenfeld]

By the way, just to clarify, when I wrote that the date format in Excel on the two different machines was different, I should have written the "default" date format. That will occur if the WRS date format on the two machines is different.

CTRL-; will enter the date (as an Excel serial number) formatted according to the WRS. However, if your date setting is different within excel, it will change it to the Excel setting. If the format of the Excel cell is "General" it will remain formatted as the WRS.

If the settings are different, you can probably see this after you hit CTRL-; and before you hit ENTER to leave the cell.

 

[Ron Rosenfeld]

Still not a clear explanation.

I should have also written that the default short date setting in Excel mirrors the WRS for short date.

 

[GS]

By the way, just to clarify, when I wrote that the date format in
Excel on the two different machines was different, I should have
written the "default" date format. That will occur if the WRS date
format on the two machines is different.

CTRL-; will enter the date (as an Excel serial number) formatted
according to the WRS. However, if your date setting is different
within excel, it will change it to the Excel setting. If the format
of the Excel cell is "General" it will remain formatted as the WRS.

If the settings are different, you can probably see this after you
hit CTRL-; and before you hit ENTER to leave the cell.

Ron,
I fully understand how dates (should) work in Excel. This is why I
suspect a problem in my particular file lies elsewhere. I can't even
say the problem still persists without further testing, but so far I
can't reproduce it in a new workbook.

I don't follow what you mean when you say the "date format in my
workbook" because it's the same workbook being opened from a network
location. Note that I haven't been watching for repeats in other
workbooks that use the default date format (regardless of OS-WRS), but
I haven't noticed any new occurances. I suppose I should open the
subject file and reset to the default format, then save & open in the
other OS. Currently, everything behaves as expected working directly
via UI input in the workbooks.

As I already mentioned, I don't have date format issues importing dates
from delimited text files!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I'd be interested to see the VBA code you use to import the data. As
I've stated more than once in this thread, I use VBA to import all
kinds of data from any form of plain text file and the formatting stays
exactly as I specify in code. Mind you, I import the data to a string
array, format the date field how I want, then dump the array into a
worksheet. At that point all data is treated as 'string' and so nothing
changes (not even leading zeros).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion