Greetings all,

Us date format: 01/10/2009 ( January 10th 2009)

1. I want it to convert to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives me
wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

1). Format cell, custom, dd-mm-yy. Note that this still requires you to input
data as you setup XL, which sounds like for you its mm/dd/yy
2). You'll need to use a formula for this. Something like this:

=LOOKUP(WEEKDAY(A2,1),{1,2,3,4,5,6,7},{"Sunday","M onday","Tuesday","Wednesday","Thursday","Friday"," Saturday"})&" Day "&DAY(A2)

could work.

I don't understand your example. Why should June 1, 2009 return Saturday?
(June 1st is a Monday)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Peter" wrote:

Greetings all,

Us date format: 01/10/2009 ( January 10th 2009)

1. I want it to convert to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives me
wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

Inthink he is referring to the weekday option to show Sunday as day 1 of the
week, as opposed to Monday being day 1.
--
HTH

Kassie

Replace xxx with hotmail


"Luke M" wrote:

1). Format cell, custom, dd-mm-yy. Note that this still requires you to input
data as you setup XL, which sounds like for you its mm/dd/yy
2). You'll need to use a formula for this. Something like this:

=LOOKUP(WEEKDAY(A2,1),{1,2,3,4,5,6,7},{"Sunday","M onday","Tuesday","Wednesday","Thursday","Friday"," Saturday"})&" Day "&DAY(A2)

could work.

I don't understand your example. Why should June 1, 2009 return Saturday?
(June 1st is a Monday)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Peter" wrote:

Greetings all,

Us date format: 01/10/2009 ( January 10th 2009)

1. I want it to convert to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives me
wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

Hi Peter,
What function are you using?

You don't appear to be using the correct date format. That may be why you're running into problems.

The ISO standard is not dd-mm-yy, it's yyyy-mm-dd.

From ISO 8601...
5.2.1.1 Complete representation
When the application identifies the need for an expression only of a calendar date, then the complete representation
shall be a single numeric data element comprising eight digits, where [YYYY] represents a calendar year, [MM] the
ordinal number of a calendar month within the calendar year, and [DD] the ordinal number of a day within the calendar
month.
Basic format: YYYYMMDD EXAMPLE 19850412
Extended format: YYYY-MM-DD EXAMPLE 1985-04-12

And...
day of the week is represented by one decimal digit. Monday shall be identified as day [1] of any calendar
week, and subsequent days of the same week shall be numbered in ascending sequence to Sunday (day [7]).

calendar week is represented by two decimal digits. The first calendar week of a year shall be identified as
[01] and subsequent weeks shall be numbered in ascending sequence.

So the first Monday of the year is the start of week 01. (week number)


John



"Peter" wrote in message ...
Greetings all,

Us date format: 01/10/2009 ( January 10th 2009)

1. I want it to convert to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives me
wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

Are you sure about "So the first Monday of the year is the start of week 01.
(week number)"?

I thought that week 01 was any one of the following equaivalent
descriptions:
a.. the week with the year's first Thursday in it (the formal ISO
definition),
b.. the week with 4 January in it,
c.. the first week with the majority (four or more) of its days in the
starting year, and
d.. the week starting with the Monday in the period 29 December - 4
January.
From those definitions, the first Monday of the year is not necessarily in
week 01.
--
David Biddulph

"jaf" wrote in message
...
Hi Peter,
What function are you using?

You don't appear to be using the correct date format. That may be why
you're running into problems.

The ISO standard is not dd-mm-yy, it's yyyy-mm-dd.

From ISO 8601...
5.2.1.1 Complete representation
When the application identifies the need for an expression only of a
calendar date, then the complete representation
shall be a single numeric data element comprising eight digits, where
[YYYY] represents a calendar year, [MM] the
ordinal number of a calendar month within the calendar year, and [DD] the
ordinal number of a day within the calendar
month.
Basic format: YYYYMMDD EXAMPLE 19850412
Extended format: YYYY-MM-DD EXAMPLE 1985-04-12

And...
day of the week is represented by one decimal digit. Monday shall be
identified as day [1] of any calendar
week, and subsequent days of the same week shall be numbered in ascending
sequence to Sunday (day [7]).

calendar week is represented by two decimal digits. The first calendar
week of a year shall be identified as
[01] and subsequent weeks shall be numbered in ascending sequence.

So the first Monday of the year is the start of week 01. (week number)


John



"Peter" wrote in message
...
Greetings all,

Us date format: 01/10/2009 ( January 10th 2009) 1. I want it to convert
to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives
me wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

OK, thanks all for the help..i think i got it figured out now...the date
display format for the ISO 8601, recommendation for IT/Data applications, is
to sort the date format in either Ascending or Descending order internally
per date day/month/year - year/month/day were the definitaion of what is to
be considered the first wek of the year is as described below...

"David Biddulph" wrote:

Are you sure about "So the first Monday of the year is the start of week 01.
(week number)"?

I thought that week 01 was any one of the following equaivalent
descriptions:
a.. the week with the year's first Thursday in it (the formal ISO
definition),
b.. the week with 4 January in it,
c.. the first week with the majority (four or more) of its days in the
starting year, and
d.. the week starting with the Monday in the period 29 December - 4
January.
From those definitions, the first Monday of the year is not necessarily in
week 01.
--
David Biddulph

"jaf" wrote in message
...
Hi Peter,
What function are you using?

You don't appear to be using the correct date format. That may be why
you're running into problems.

The ISO standard is not dd-mm-yy, it's yyyy-mm-dd.

From ISO 8601...
5.2.1.1 Complete representation
When the application identifies the need for an expression only of a
calendar date, then the complete representation
shall be a single numeric data element comprising eight digits, where
[YYYY] represents a calendar year, [MM] the
ordinal number of a calendar month within the calendar year, and [DD] the
ordinal number of a day within the calendar
month.
Basic format: YYYYMMDD EXAMPLE 19850412
Extended format: YYYY-MM-DD EXAMPLE 1985-04-12

And...
day of the week is represented by one decimal digit. Monday shall be
identified as day [1] of any calendar
week, and subsequent days of the same week shall be numbered in ascending
sequence to Sunday (day [7]).

calendar week is represented by two decimal digits. The first calendar
week of a year shall be identified as
[01] and subsequent weeks shall be numbered in ascending sequence.

So the first Monday of the year is the start of week 01. (week number)


John



"Peter" wrote in message
...
Greetings all,

Us date format: 01/10/2009 ( January 10th 2009) 1. I want it to convert
to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives
me wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!

Hi David,
No. I screwed up. You're correct.

A calendar week is identified within a calendar year by the calendar week number. This is its ordinal position within
the year, applying the rule that the first calendar week of a year is the one that includes the first Thursday of that year
and that the last calendar week of a calendar year is the week immediately preceding the first calendar week of the
next calendar year.
NOTE 1 These rules provide for a calendar year to have 52 or 53 calendar weeks;
NOTE 2 The first calendar week of a calendar year may include up to three days from the previous calendar year; the last
calendar week of a calendar year may include up to three days from the following calendar year;
NOTE 3 The time-interval formed by the week dates of a calendar year is not the same as the time-interval formed by the
calendar dates or ordinal dates for the same year. For instance:
- Sunday 1995 January 1 is the 7th day of the 52nd week of 1994, and
- Tuesday 1996 December 31 is the 2nd day of the 1st week 1997.
NOTE 4 The rule for determining the first calendar week is equivalent with the rule "the first calendar week is the week which
includes January 4".

John



"David Biddulph" <groups [at] biddulph.org.uk wrote in message ...
Are you sure about "So the first Monday of the year is the start of week 01.
(week number)"?

I thought that week 01 was any one of the following equaivalent
descriptions:
a.. the week with the year's first Thursday in it (the formal ISO
definition),
b.. the week with 4 January in it,
c.. the first week with the majority (four or more) of its days in the
starting year, and
d.. the week starting with the Monday in the period 29 December - 4
January.
From those definitions, the first Monday of the year is not necessarily in
week 01.
--
David Biddulph

"jaf" wrote in message
...
Hi Peter,
What function are you using?

You don't appear to be using the correct date format. That may be why
you're running into problems.

The ISO standard is not dd-mm-yy, it's yyyy-mm-dd.

From ISO 8601...
5.2.1.1 Complete representation
When the application identifies the need for an expression only of a
calendar date, then the complete representation
shall be a single numeric data element comprising eight digits, where
[YYYY] represents a calendar year, [MM] the
ordinal number of a calendar month within the calendar year, and [DD] the
ordinal number of a day within the calendar
month.
Basic format: YYYYMMDD EXAMPLE 19850412
Extended format: YYYY-MM-DD EXAMPLE 1985-04-12

And...
day of the week is represented by one decimal digit. Monday shall be
identified as day [1] of any calendar
week, and subsequent days of the same week shall be numbered in ascending
sequence to Sunday (day [7]).

calendar week is represented by two decimal digits. The first calendar
week of a year shall be identified as
[01] and subsequent weeks shall be numbered in ascending sequence.

So the first Monday of the year is the start of week 01. (week number)


John



"Peter" wrote in message
...
Greetings all,

Us date format: 01/10/2009 ( January 10th 2009) 1. I want it to convert
to ISO 8601 standard: dd-mm-yy
2. I want the day number (Sunday Day 1)..

I am trying all kind formats..customs..regional settings...but it gives
me wrong results..so fora example...01 June 2009...returns Saturday...

Thanks!