Programming, Excel

[formula]
Formatting
Logical

[macros]
Case changing
Copy formula down range

'Formatting'

Dates, Weekdays 'dates, days of week'

Format the Current Date as the Day of the Week for you i.e. if Today was Saturday, Excel Cell will display Saturday.

 copy code to clipboard.

=TEXT(TODAY(), "dddd")



To format for today (if Today was 02.05.2008) as Friday, 02 May 2008.

 copy code to clipboard.

=TEXT(TODAY(),"dddd, dd mmmm yyyy")



To force a format for Sunday 4 May 2008, where 108 is added to 1900 for year 2008, 5 is May & 4 is 4th.

 copy code to clipboard.

=TEXT(DATE(108,5,4),"dddd, dd mmmm yyyy")



Where you have a date formatted in a certain way, convert it using the TEXT function, i.e. Cell C4 contains 2007-01-01 - convert to 01.01.2007.

 copy code to clipboard.

=TEXT(C4,"dd.mm.yyyy")



Weekdays can be derived by obtaining the numerical day of week i.e. Cell C4 contains 01.01.2007 - the following formula returns 2, meaning Monday.

 copy code to clipboard.

=WEEKDAY(C4)



Then either use a vlookup to the numeric value in a table to return a pre-defined day of the week or the following formula to convert into the correct Day. This will reformat the numerical value 2 (see the example above where Cell C4 contains 01.01.2007) into the corresponding day of the week i.e. Monday.

 copy code to clipboard.

=TEXT(WEEKDAY(C4),"dddd")



Numbers & Padding 'numbers, padding out fields'

To force numbers of any length into pre-defined sizes i.e. 12 or 125 into '000012' or '000125' use the following where Cell A1 contains the number and Cell B1 the formula shown below.

 copy code to clipboard.

=TEXT(A1,"000000")