Date format

[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.-

 

[Eduardo]

Hi,
use

=TEXT(MONTH(A1),"dddd")

 

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

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

 

[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.

 

[Jim Thomlinson]

By way of proof try this...

=TEXT(MONTH(A1),"mm dd yyyy dddd")

 

[Emece]

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.-

 

[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

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.-

 

[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

 

[Jim Thomlinson]

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

Jim Thomlinson

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

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

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.-

.