Date format in Excel 2007
Solved
f1gp
Posted messages
27
Status
Membre
-
lilouwe -
lilouwe -
Hello,
Despite searching through Excel's help, I can't get the following result:
In row 2, I have dates (from 01/01/2010 to 31/01/2010) and I've applied the custom format "j" to get the day as a number in the form: 1, 2, 3... I want to obtain in row 1 the first letter of the day in uppercase.
Example: A2 = 1 (for 01/01/2010) --> A1 = F (for Friday in uppercase)
Example: B2 = 2 (for 02/01/2010) --> B1 = S (for Saturday in uppercase)
I have tried with the "=TEXT..." variations but didn't achieve the desired result
Despite searching through Excel's help, I can't get the following result:
In row 2, I have dates (from 01/01/2010 to 31/01/2010) and I've applied the custom format "j" to get the day as a number in the form: 1, 2, 3... I want to obtain in row 1 the first letter of the day in uppercase.
Example: A2 = 1 (for 01/01/2010) --> A1 = F (for Friday in uppercase)
Example: B2 = 2 (for 02/01/2010) --> B1 = S (for Saturday in uppercase)
I have tried with the "=TEXT..." variations but didn't achieve the desired result
Configuration: Windows Vista Internet Explorer 7.0
2 réponses
in order:
1) WEEKDAY() returns the day of the week of the date as a number from 1 to 7 (with 1=Sunday by default)
2) TEXT(...;"dddd"): converts this week number into text (the cell formatting "dddd" is needed to get text like Monday, Tuesday, etc.)
3) LEFT(...;1): retrieves the first character of the text in question
4) UPPER(): as its name indicates...
This can work with IF statements but it's a bit cumbersome because it requires stacking 7 levels of nesting by repeating the cell reference 7 times....(=IF(WEEKDAY(A1)=1;"L";IF(WEEKDAY(A1)=2; ...etc). To simplify, it's better to use an intermediate cell = WEEKDAY(A1) to lighten the writing...
--
If you need nothing, just ask me.
1) WEEKDAY() returns the day of the week of the date as a number from 1 to 7 (with 1=Sunday by default)
2) TEXT(...;"dddd"): converts this week number into text (the cell formatting "dddd" is needed to get text like Monday, Tuesday, etc.)
3) LEFT(...;1): retrieves the first character of the text in question
4) UPPER(): as its name indicates...
This can work with IF statements but it's a bit cumbersome because it requires stacking 7 levels of nesting by repeating the cell reference 7 times....(=IF(WEEKDAY(A1)=1;"L";IF(WEEKDAY(A1)=2; ...etc). To simplify, it's better to use an intermediate cell = WEEKDAY(A1) to lighten the writing...
--
If you need nothing, just ask me.
And THANK YOU for the response!!!
I thought I would have to use a multi-level IF function, but in the end you have a very effective function. I wonder which element of this function allows the display of the first letter of the day? "dddd"??? Anyway, I have the solution and that's the most important thing
Best regards and thanks again.