Solved

Zellers congruance in Excel

Posted on 2000-04-04
3
248 Views
Last Modified: 2012-05-04
Does anyone have an algorithm to calculate which day of the week a date falls on in Excel?  
0
Comment
Question by:cotec1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
Chrynoble earned 50 total points
ID: 2684798
Yes,

in a worksheet
=weekday(B4)

in VBA
Weekday(MyDateVar)

Sunday is 1, Monday 2, etc....
0
 
LVL 5

Expert Comment

by:TigerMan
ID: 2686530
cotec1,

The weekday function can return various ranges of values - dependent on the second parameter that you pass to the function, i.e.

= WeekDay (B4,1) will return a number from 1(Sunday) to 7(Saturday) [this is also the default value if you don't specify a parameter],

= Weekday (B4,2) will return a number from 1(Monday to 7(Sunday), and

= Weekday (B4,3) returns a number from 0(Monday) to 6(Sunday).

After you determine the numeric value of the weekday you are working with, by using the above =weekday(B4) worksheet function, you might then like to determine what the name of the weekday is.

The easiest way that I have found to accomplish that is by setting up a little table somewhere.  Using the 1(Sunday) to 7(Saturday) model, your table might look like:

1  Sunday
2  Monday
3
4
5
6
7  Saturday

Select all these cells and Name the range as WeekdayTable or similar.

Then to determine the name of the day, in another cell enter the formula:

= VLookUp(C4, WeekDayTable, 2).

But when its all said and done, by far the easiest way to determine the weekday is by using:

=TEXT("" & B4, "dddd")

This will return the day name from a date field in B4.


Crynoble, it is not normal practice to enter responses as Answers unless you are dead certain that your response exactly fits the question.  It is usual practice to just use the commenting facility.


Dave

0
 
LVL 9

Expert Comment

by:antrat
ID: 2688294
Hi cotec1

Suppose your date is in cell A1 all you would have to do is in any cell put =A1 then go to Format>Cells>Number>Custom and using anyone of the pre-defined formats as a starting point, type dddd.

This will now convert the date to the day of the week that it falls. e.g if the date is a Saturday then dddd format will return Saturday. ddd will return Sat and dd or d will return the day of the month.

Of course you could just applt]y this format to A1.

Good luck

antrat
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question