Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Calculating weekdays

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Calculating weekdays

Resolved · Medium Priority · Version 2010

Reply Tue 17 Jan 2012, 15:29Delegate Lucy said...

Lucy has attended:
Excel Intermediate course
Excel Advanced course

Calculating weekdays

Hi there

Had an excellent training session but unfortunately have forgotten one of the most important bits!

I have a column of dates and next to it a column with the corresponding day of the week.

I've been able to create a column that displays the weekday but it also has a strange date next to it (1900s) using this formula:

=WEEKDAY([@Date])

Can someone remind me of the formula I need to use to display a weekday in a separate column to the date please

Thanks

For upcoming training course dates see: Pricing & availability

Reply Fri 20 Jan 2012, 11:36Trainer Paul said...

RE: Calculating weekdays

To return the weekday of any date in Excel we can use the WEEKDAY formula. The day is given as an whole number, ranging from 1 (Sunday) to 7 (Saturday), by default.

The formula syntax for the WEEKDAY formula is;

WEEKDAY(serial_number,return_type)

Serial_num is any valid date. See how Excel stores dates & times .
Return_type is a number (between 1 and 3) that determines the type of return value. 1, or omitted = 1 (Sunday) through 7 (Saturday). 2 = 1 (Monday) through 7 (Sunday). 3 = 0 (Monday) through 6 (Sunday).

An example of WEEKNUM formula is as shown below

=WEEKDAY(A1)/WEEKDAY(A1,1)

Reply Fri 20 Jan 2012, 11:54Delegate Lucy said...

RE: Calculating weekdays

Hi

Thanks very much for replying. Using the above I can get the numerical value but I now need to turn this into the weekday in words, which I can't seem to do with the above information.

Are you able to help with this please?

(I should point out I am only a beginner/intermidiate user of Excel so if you could possibly explain it in laymen's terms that would be great!)

Many Thanks

Reply Fri 20 Jan 2012, 12:30Trainer Paul said...

RE: Calculating weekdays

I would do the following;

lets say you have 20/01/12 in A1 and you want "Friday" in B1. (is that what you want to do?)

in this case go to B1, enter the formula =A1. Then format B1 from the "Numbers tab" in "format cells" (right click to get the format cells option), choose custom and manually enter DDDD.

Let me know if you have any issues.

Reply Fri 20 Jan 2012, 13:01Delegate Lucy said...

RE: Calculating weekdays

It worked!

That's brilliant! Thanks very much for your help

Kind Regards

Lucy

Excel articles

· How Excel Can Transform All Of Your Office Documents
· How To Create An Excel 2010 Chart To Show Both A Number And A Percent Data Series
· Combining Logical Functions In Excel
· How To Split A Stacked Chart In Excel
· The Mysteries Of Excel Explained

Please browse our web site to find out more about
vba for excel training and other Microsoft training courses.

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.

View all Excel hints and tips