Local Date Format Change

[RobV]

I have a VBA macro that works with task dates. Some schedules use the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to misbehave. How
do I force the dates into a single format prior to processing?

 

[John]

I have a VBA macro that works with task dates. Some schedules use the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to misbehave. How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[RobV]

John, that solved my problem. Thank you.

I have a VBA macro that works with task dates. Some schedules use the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to misbehave. How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[John]

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

I have a VBA macro that works with task dates. Some schedules use the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[Scudder]

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan is
scanned for status update (including dates) via the MS Project object model.

The dates are always in a consistent (US) format, this is as they should be
since MS Office applications convert dates to use local formatting regardless
of where they originate.

How and where exactly are you encountering these multiple date formats ?

Lee.

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

I have a VBA macro that works with task dates. Some schedules use the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[Rod Gill]

Hi,

When in a mixed date format, I always specify a standard that all dates must
have a minimum of a 3 character month to avoid confusion. EG 23-Aug-2007

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[John]

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan is
scanned for status update (including dates) via the MS Project object model.

The dates are always in a consistent (US) format, this is as they should be
since MS Office applications convert dates to use local formatting regardless
of where they originate.

How and where exactly are you encountering these multiple date formats ?

Scudder,
Depending on which fields contain the dates, they will NOT automatically
take on the system setting for date format. For example, if dates are
contained in spare fields, they are treated as text.

The other possibility is that Rob's macro is being used by his Canadian
coworkers on THEIR PCs, which are most likely set for Canadian date
format.

John
Project MVP

Lee.

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

:

I have a VBA macro that works with task dates. Some schedules use
the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to
misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is
important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use
string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[Scudder]

Ah, that clarifies it. Thanks John.

I thought I was missing something there.

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan is
scanned for status update (including dates) via the MS Project object model.

The dates are always in a consistent (US) format, this is as they should be
since MS Office applications convert dates to use local formatting regardless
of where they originate.

How and where exactly are you encountering these multiple date formats ?

Scudder,
Depending on which fields contain the dates, they will NOT automatically
take on the system setting for date format. For example, if dates are
contained in spare fields, they are treated as text.

The other possibility is that Rob's macro is being used by his Canadian
coworkers on THEIR PCs, which are most likely set for Canadian date
format.

John
Project MVP

Lee.

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

:

I have a VBA macro that works with task dates. Some schedules use
the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order and
presentation for month and day. This is causing the macro to
misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your code.
Most normal use of dates is independent of the date order. However in
certain circumstances, (and I've had to deal with them), it is
important
to know the date order and sometimes even the date format (i.e. with or
without time, short year or long year, etc.). Here is what I suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use
string
manipulation to convert the date order. I find the Month, Day, and Year
functions along with the Mid function to be very useful for rearranging
dates.

Hope this helps.
John
Project MVP

 

[John]

Ah, that clarifies it. Thanks John.

I thought I was missing something there.

Scudder,
You are welcome and thanks for the feedback.
John

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan is
scanned for status update (including dates) via the MS Project object
model.

The dates are always in a consistent (US) format, this is as they should
be
since MS Office applications convert dates to use local formatting
regardless
of where they originate.

How and where exactly are you encountering these multiple date formats ?

Scudder,
Depending on which fields contain the dates, they will NOT automatically
take on the system setting for date format. For example, if dates are
contained in spare fields, they are treated as text.

The other possibility is that Rob's macro is being used by his Canadian
coworkers on THEIR PCs, which are most likely set for Canadian date
format.

John
Project MVP

Lee.

:

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

:

I have a VBA macro that works with task dates. Some schedules
use
the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order
and
presentation for month and day. This is causing the macro to
misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your
code.
Most normal use of dates is independent of the date order. However
in
certain circumstances, (and I've had to deal with them), it is
important
to know the date order and sometimes even the date format (i.e.
with or
without time, short year or long year, etc.). Here is what I
suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use
string
manipulation to convert the date order. I find the Month, Day, and
Year
functions along with the Mid function to be very useful for
rearranging
dates.

Hope this helps.
John
Project MVP

 

[RobV]

John's last scenario is the correct one. Canadian PCs have the regional
settings defaulted to English - Canada.

Ah, that clarifies it. Thanks John.

I thought I was missing something there.

Scudder,
You are welcome and thanks for the feedback.
John

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan is
scanned for status update (including dates) via the MS Project object
model.

The dates are always in a consistent (US) format, this is as they should
be
since MS Office applications convert dates to use local formatting
regardless
of where they originate.

How and where exactly are you encountering these multiple date formats ?
Scudder,
Depending on which fields contain the dates, they will NOT automatically
take on the system setting for date format. For example, if dates are
contained in spare fields, they are treated as text.

The other possibility is that Rob's macro is being used by his Canadian
coworkers on THEIR PCs, which are most likely set for Canadian date
format.

John
Project MVP

Lee.

:

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

:

I have a VBA macro that works with task dates. Some schedules
use
the
US-English Regional setting as the default format, and others use
Canadian-English format. The two formats have a different order
and
presentation for month and day. This is causing the macro to
misbehave.
How
do I force the dates into a single format prior to processing?

Robv,
It kind of depends on how you are using the date values in your
code.
Most normal use of dates is independent of the date order. However
in
certain circumstances, (and I've had to deal with them), it is
important
to know the date order and sometimes even the date format (i.e.
with or
without time, short year or long year, etc.). Here is what I
suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order, use
string
manipulation to convert the date order. I find the Month, Day, and
Year
functions along with the Mid function to be very useful for
rearranging
dates.

Hope this helps.
John
Project MVP

 

[John]

John's last scenario is the correct one. Canadian PCs have the regional
settings defaulted to English - Canada.

RobV,
I thought that was the case based on your first post, but I thought it
relevant to also list other reasons why date formats might cause
problems.

John

Ah, that clarifies it. Thanks John.

I thought I was missing something there.

Scudder,
You are welcome and thanks for the feedback.
John

:

Rob, John,

Now I'm intrigued...

I receive project plans from global divisions every week, each plan
is
scanned for status update (including dates) via the MS Project object
model.

The dates are always in a consistent (US) format, this is as they
should
be
since MS Office applications convert dates to use local formatting
regardless
of where they originate.

How and where exactly are you encountering these multiple date
formats ?
Scudder,
Depending on which fields contain the dates, they will NOT
automatically
take on the system setting for date format. For example, if dates are
contained in spare fields, they are treated as text.

The other possibility is that Rob's macro is being used by his Canadian
coworkers on THEIR PCs, which are most likely set for Canadian date
format.

John
Project MVP

Lee.

:

John, that solved my problem. Thank you.

RobV,
You're welcome and thanks for the feedback.
John

:


I have a VBA macro that works with task dates. Some
schedules
use
the
US-English Regional setting as the default format, and others
use
Canadian-English format. The two formats have a different
order
and
presentation for month and day. This is causing the macro to
misbehave.
How
do I force the dates into a single format prior to
processing?

Robv,
It kind of depends on how you are using the date values in your
code.
Most normal use of dates is independent of the date order.
However
in
certain circumstances, (and I've had to deal with them), it is
important
to know the date order and sometimes even the date format (i.e.
with or
without time, short year or long year, etc.). Here is what I
suggest.
Use the following test at the beginning of your code:
If application.DateOrder <> pjMonthDayYear Then
[set a flag]
End If

Then, for lines of code that are sensitive to the date order,
use
string
manipulation to convert the date order. I find the Month, Day,
and
Year
functions along with the Mid function to be very useful for
rearranging
dates.

Hope this helps.
John
Project MVP