If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date format
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.- |
#2
|
|||
|
|||
Date format
Hi,
use =TEXT(MONTH(A1),"dddd") "Emece" wrote: 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.- |
#3
|
|||
|
|||
Date format
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 [b]. Micky "Emece" wrote: 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.- |
#4
|
|||
|
|||
Date format
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...onal_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. -- HTH... Jim Thomlinson "Eduardo" wrote: Hi, use =TEXT(MONTH(A1),"dddd") "Emece" wrote: 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.- |
#5
|
|||
|
|||
Date format
By way of proof try this...
=TEXT(MONTH(A1),"mm dd yyyy dddd") -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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...onal_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. -- HTH... Jim Thomlinson "Eduardo" wrote: Hi, use =TEXT(MONTH(A1),"dddd") "Emece" wrote: 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.- |
#6
|
|||
|
|||
Date format
Yes, I meant DAY. Thanks for noticing it and for your help.
"מיכאל (מיקי) אבידן" wrote: 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 [b]. Micky "Emece" wrote: 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.- |
#7
|
|||
|
|||
Date format
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" wrote: Yes, I meant DAY. Thanks for noticing it and for your help. "מיכאל (מיקי) אבידן" wrote: 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 [b]. Micky "Emece" wrote: 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.- |
#8
|
|||
|
|||
Date format
On Tue, 30 Mar 2010 08:59:02 -0700, Emece
wrote: 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 |
#9
|
|||
|
|||
Date format
DAY returns 1 to 31, not 1 to 7, Jim, but it's still the wrong answer as you
said. -- David Biddulph "Jim Thomlinson" wrote in message ... 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" wrote: Yes, I meant DAY. Thanks for noticing it and for your help. "????? (????) ?????" wrote: 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 [b]. Micky "Emece" wrote: 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.- |
#10
|
|||
|
|||
Date format
Thanks... WeekDay... not Day. You are absolutely correct.
-- HTH... Jim Thomlinson "David Biddulph" wrote: DAY returns 1 to 31, not 1 to 7, Jim, but it's still the wrong answer as you said. -- David Biddulph "Jim Thomlinson" wrote in message ... 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" wrote: Yes, I meant DAY. Thanks for noticing it and for your help. "????? (????) ?????" wrote: 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 [b]. Micky "Emece" wrote: 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.- . |
Thread Tools | |
Display Modes | |
|
|