+ Reply to Thread
Results 1 to 7 of 7

Dates and days of the week...

  1. #1
    Registered User
    Join Date
    07-25-2010
    Location
    PA
    MS-Off Ver
    Excel 365
    Posts
    41

    Dates and days of the week...

    Hello all,

    I have a huge worksheet with multiple dates listed like this...

    1/1/2001
    1/2/2001
    1/3/2001
    ...
    3/16/2016


    on another tab I have
    Monday,
    Tuesday,
    Wednesday,
    Thursday,
    Friday

    I want to put a formula in the cell next to the weekday that basically says if date = Monday give me sum of all Mondays.
    same for Tuesday... etc.

    Please help!
    Thank you all.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dates and days of the week...

    Quote Originally Posted by shhhhh22 View Post
    give me sum of all Mondays.
    Do you mean return the COUNT of all Mondays?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dates and days of the week...

    See if this is what you had in mind...

    Data Range
    A
    B
    C
    D
    E
    1
    Date
    Weekday
    Count
    2
    12/30/2009
    Monday
    1
    3
    6/18/2002
    Tuesday
    3
    4
    9/7/2004
    Wednesday
    3
    5
    11/21/2013
    Thursday
    3
    6
    4/17/2014
    Friday
    0
    7
    9/1/2002
    Saturday
    2
    8
    6/5/2011
    Sunday
    2
    9
    8/5/2009
    ------
    ------
    ------
    ------
    10
    9/19/2013
    11
    3/26/2012
    12
    9/12/2009
    13
    4/22/2000
    14
    2/1/2012
    15
    12/30/2014


    This formula entered in E2 and copied down:

    =SUMPRODUCT(--(TEXT(A$2:A$15,"dddd")=D2))

  4. #4
    Registered User
    Join Date
    07-25-2010
    Location
    PA
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Dates and days of the week...

    thank you Tony... I should have been more clear. If we assume there are 10 years worth of dates in column "A" just as you have... in column "B" is a value. I want to add those values based on day of the week.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dates and days of the week...

    Like this...

    Data Range
    A
    B
    C
    D
    E
    1
    Date
    Value
    Weekday
    Total
    2
    12/30/2009
    37
    Monday
    78
    3
    6/18/2002
    74
    Tuesday
    204
    4
    9/7/2004
    46
    Wednesday
    164
    5
    11/21/2013
    54
    Thursday
    165
    6
    4/17/2014
    16
    Friday
    0
    7
    9/1/2002
    3
    Saturday
    39
    8
    6/5/2011
    63
    Sunday
    66
    9
    8/5/2009
    43
    ------
    ------
    ------
    10
    9/19/2013
    95
    11
    3/26/2012
    78
    12
    9/12/2009
    10
    13
    4/22/2000
    29
    14
    2/1/2012
    84
    15
    12/30/2014
    84


    This formula entered in E2 and copied down:

    =SUMPRODUCT(--(TEXT(A$2:A$15,"dddd")=D2),B$2:B$15)

  6. #6
    Registered User
    Join Date
    07-25-2010
    Location
    PA
    MS-Off Ver
    Excel 365
    Posts
    41

    Re: Dates and days of the week...

    This is perfect. Bless you. Thanks!

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dates and days of the week...

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] number of days between dates by week ending date
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2015, 12:19 AM
  2. [SOLVED] Subtract two dates to get # of week days - networkdays does not quite work
    By loree1223 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-10-2013, 10:17 PM
  3. [SOLVED] How to Create a Chart using days of week, from a series of dates
    By Dan86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2013, 12:10 AM
  4. Replies: 4
    Last Post: 02-24-2012, 11:06 AM
  5. Difference between two dates in week and days
    By newbiekinsey in forum Excel General
    Replies: 8
    Last Post: 06-06-2011, 06:02 PM
  6. [SOLVED] Applying days of the week to dates
    By will in forum Excel General
    Replies: 1
    Last Post: 12-05-2005, 11:40 AM
  7. Sorting Dates in Days of the Week
    By Eagle784 in forum Excel General
    Replies: 3
    Last Post: 08-19-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1