Date format

E

Emece

I have a column with dates in the following format: 30/03/2010 (Spanish date
format) I want to display in another column only the month, and in format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-
 
×

מיכ×ל (מיקי) ×בידן

I assume you meant DAY and not MONTH.
If A1 holds the first date . in B1 type: =Day(A1) and format, column "B", as
dddd.
Pls nothoce that you can also Custom Format the dates in col. "A" in order
to avoid the helper column .
Micky
 
J

Jim Thomlinson

Not to burst your bubble but that formula will return a very wrong result.
The Month formula will return a number from 1 to 12 representing the months
of the year. Since XL stores dates as the number of days since Jan 1 1900
what you actually have is Jan 1 through Jan 12 1900. You will get back the
days of the week for those dates. The final problem is that XL has the wrong
days of the week for those dates as an intentional bug.

http://spreadsheetpage.com/index.php/oddity/the_intentional_date_bug/

The question in itself does not make sense. If you only get the month for
the date then the day of the week is lost at that point.
 
E

Emece

Yes, I meant DAY. Thanks for noticing it and for your help.

מיכ×ל (מיקי) ×בידן said:
I assume you meant DAY and not MONTH.
If A1 holds the first date . in B1 type: =Day(A1) and format, column "B", as
dddd.
Pls nothoce that you can also Custom Format the dates in col. "A" in order
to avoid the helper column .
Micky


Emece said:
I have a column with dates in the following format: 30/03/2010 (Spanish date
format) I want to display in another column only the month, and in format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-
 
J

Jim Thomlinson

Same issue if you use the Day formula as using the month fromula. It returns
the numbers 1 through7 which will correspond to Jan 1 - Jan 7 1900. Just
format the date without the day function to get the proper day of the week.
--
HTH...

Jim Thomlinson


Emece said:
Yes, I meant DAY. Thanks for noticing it and for your help.

מיכ×ל (מיקי) ×בידן said:
I assume you meant DAY and not MONTH.
If A1 holds the first date . in B1 type: =Day(A1) and format, column "B", as
dddd.
Pls nothoce that you can also Custom Format the dates in col. "A" in order
to avoid the helper column .
Micky


Emece said:
I have a column with dates in the following format: 30/03/2010 (Spanish date
format) I want to display in another column only the month, and in format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-
 
R

Ron Rosenfeld

I have a column with dates in the following format: 30/03/2010 (Spanish date
format) I want to display in another column only the month, and in format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-

With Date in A1

Select column B
Format/number/custom: dddd

B1: =A1

Do NOT use the MONTH (or DAY) function in B1

--ron
 
D

David Biddulph

DAY returns 1 to 31, not 1 to 7, Jim, but it's still the wrong answer as you
said.
--
David Biddulph


Jim Thomlinson said:
Same issue if you use the Day formula as using the month fromula. It
returns
the numbers 1 through7 which will correspond to Jan 1 - Jan 7 1900. Just
format the date without the day function to get the proper day of the
week.
--
HTH...

Jim Thomlinson


Emece said:
Yes, I meant DAY. Thanks for noticing it and for your help.

????? (????) ????? said:
I assume you meant DAY and not MONTH.
If A1 holds the first date . in B1 type: =Day(A1) and format, column
"B", as
dddd.
Pls nothoce that you can also Custom Format the dates in col. "A" in
order
to avoid the helper column .
Micky


:

I have a column with dates in the following format: 30/03/2010
(Spanish date
format) I want to display in another column only the month, and in
format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-
 
J

Jim Thomlinson

Thanks... WeekDay... not Day. You are absolutely correct.
--
HTH...

Jim Thomlinson


David Biddulph said:
DAY returns 1 to 31, not 1 to 7, Jim, but it's still the wrong answer as you
said.
--
David Biddulph


Jim Thomlinson said:
Same issue if you use the Day formula as using the month fromula. It
returns
the numbers 1 through7 which will correspond to Jan 1 - Jan 7 1900. Just
format the date without the day function to get the proper day of the
week.
--
HTH...

Jim Thomlinson


Emece said:
Yes, I meant DAY. Thanks for noticing it and for your help.

:

I assume you meant DAY and not MONTH.
If A1 holds the first date . in B1 type: =Day(A1) and format, column
"B", as
dddd.
Pls nothoce that you can also Custom Format the dates in col. "A" in
order
to avoid the helper column .
Micky


:

I have a column with dates in the following format: 30/03/2010
(Spanish date
format) I want to display in another column only the month, and in
format
dddd, so that it shows Tuesday.

I do this in to steps:
1. =month(date)
2. Change the format to a custom one: dddd

Is there a way to do this in only one step?

Thanks in advance

Regards,
Emece.-



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top