+ Reply to Thread
Results 1 to 3 of 3

Find day of the week of today and display the date of the selected day?

  1. #1
    Registered User
    Join Date
    03-09-2014
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Find day of the week of today and display the date of the selected day?

    I may be making this more complicated than it needs to be but I can't seem to figure it out. The goal of this spreadsheet is for General Managers of a restaurant to give feedback about food deliveries. These deliveries usually come on Monday, Thursday, and Saturday, but could for a list of reasons come on any day of the week. I want to automatically populate as much information as possible without requiring GMs to remember and enter dates.

    A1 is =TODAY()
    A2:A5 is a drop down where the GM selects the day of the week the delivery arrived. i.e. MON, TUE, WED, etc. I want the corresponding date of the past week to auto-populate in this cell (or even the adjacent cell if necessary) once the day is selected. For example, if today is 3/8/2014, when MON is selected in A2, I want the cell to automatically add "3/3/2014," making the entire cell read "MON 3/3/2014."

    So if said GM is filling in this spreadsheet on a Saturday, and I want to display the date of the previous Monday, I need to find the day of the week of A1 (WEEKDAY function), and make A2 = A1-5.
    If today is a Friday, it would be A2=A1-4, and so on for the 7 days of the week.

    Then I would need to do all of this for if a Tuesday is selected in the drop down box, meaning IF(Saturday)Then A2=A1-4, etc...

    I was trying to string together IF statements like this:

    =IF(WEEKDAY(TODAY()=7),A1-5), IF(WEEKDAY(TODAY()=6),A1-4)

    It works when I do only the first IF statement, but when I add another it returns #VALUE. I thought that excel would find the first true value and stop evaluating.

    I hope someone can help, and thank you in advance!
    Last edited by over10; 03-09-2014 at 03:19 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,613

    Re: Find day of the week of today and display the date of the selected day?

    first - there is a kind of mess with parentheses
    try the following way:
    1 day:
    =IF(WEEKDAY(TODAY())=7,A1-5,"not Sat")
    2 days:
    =IF(WEEKDAY(TODAY())=7,A1-5,IF(WEEKDAY(TODAY())=6,A1-4,"neither Sat nor Fri"))
    3 days ...
    =IF(WEEKDAY(TODAY())=7,A1-5,IF(WEEKDAY(TODAY())=6,A1-4,IF(WEEKDAY(TODAY())=5,A1-3,"other options here similar way")))

    PS. The important thing to note is that if your GM saves the file today and you open it tomorrow, excel would automaticaly change the value returned by TODAY function from Mar 09 to Mar 10 (that's is usually accepted ;-) ) and (this makes problems) will change the results of any formula, which uses TODAY.
    Last edited by Kaper; 03-09-2014 at 03:42 AM.
    Best Regards,

    Kaper

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find day of the week of today and display the date of the selected day?

    If A1 is a date and A2 is a "short" day in text format like "Sat" or "Wed". this formula in A3 will give you the previous date for that day

    =A1-WEEKDAY(A1-MATCH(A2,{"sun","mon","tue","wed","thu","fri","sat"},0))

    If A1 is a Monday and A2 says "Mon" that will give you the date 7 days before A2, if you want it to give you the A2 date change to this version

    =A1+1-WEEKDAY(A1-MATCH(A2,{"mon","tue","wed","thu","fri","sat","sun"},0))
    Audere est facere

+ 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. workbook open then show week tab according today date
    By robrobet in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-04-2013, 04:20 PM
  2. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  3. update date of the week/month compared to today
    By tbar05 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2012, 05:24 AM
  4. Replies: 2
    Last Post: 02-02-2011, 06:47 AM
  5. Replies: 3
    Last Post: 02-26-2009, 04:28 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