+ Reply to Thread
Results 1 to 11 of 11

Display text dependent upon today's date

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Display text dependent upon today's date

    Hi guys,

    In my cells a13 AND A16 I want to have the text displayed:

    We are in working, P8 (cell A13)

    We are in Delivering, P7 (cell A16)

    Basicaly you can see I have a calendar (i am open to suggestions to amend the calendar format) and i want the value from column B for working (P8) to be returned depending on the date in the same line

    in b9, you see p9 which should be returned if today's date is between mon 26th - sat 31st july. I hope this makes sense.. I have put what I am hoping to see in cellsA13 AND A16

    Thank you for helping!
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,717

    Re: Display text dependent upon today's date

    Try in A13
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In A16
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Re: Display text dependent upon today's date

    hI FlameRetired

    Thank you for this, I`m not sure if this is the case can you confirm/verify, that this would return P8 or p9 by checking today's date too? because if today's date could be p8 or p9 depending on the week..... ?

    thank yiou

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Display text dependent upon today's date

    I have an alternative formula (see file), but I do not see why you expect A16 to return P7, when cell F6 is blank. What am i missing??
    Attached Files Attached Files
    Glenn



  5. #5
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Re: Display text dependent upon today's date

    Hi Glenn,

    Your suggestion looks great, It's actually due to the way the original calendar looks .. I`ll change the blank values to ensure p7 is input

    I`m now going to apply this formula to the rest of my calendar. thank you so much

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Display text dependent upon today's date

    Grand job! Thanks for the feedback!

  7. #7
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Re: Display text dependent upon today's date

    Hi Glenn/all,

    I wanted to re-open this thread as I have a slightly updated query.

    I have attacjhed the sheet with all the months (jan 2021-jan 2022). The 'how it is' tab shows how the layout is currently on my master sheet.

    I am happy for the formula to be easier etc to change this layout so all the months fall under one another rather than side by side. I think this would also work better for Glenn's formula:

    ="We are in Working, "&INDEX(B:B,SUMPRODUCT(--(DAY(TODAY())=$H$4:$N$9)*ROW($F$4:$F$9))) (everything can be detected from column B)

    and also column F
    ="We are in Delivering, "&INDEX(F:F,SUMPRODUCT(--(DAY(TODAY())=$H$4:$N$9)*ROW($H$4:$H$9)))

    However,, I am struggling to understand how to update the formula to take into account the rest of the months now... so can anyone guide me accordingly please?

    On the 'is this how i should do it' tab i have inserted the formulas I am trying to update in the blue box.. cell A122

    Thank you
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Display text dependent upon today's date

    Wrong sheet attached?? This one shows only July...

  9. #9
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Re: Display text dependent upon today's date

    sorry - two files same name, reattached.

    thank you Glenn
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Display text dependent upon today's date

    One way...
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    753

    Re: Display text dependent upon today's date

    Thank you Glenn, a bit more complex than I anticipated but this should do the trick.. thank you

+ 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. Replies: 4
    Last Post: 08-24-2016, 09:29 AM
  2. How to display the column containing today's date
    By Karenmh in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-28-2014, 10:23 AM
  3. Hpw to display result if date is prior to today
    By aliceinnz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-07-2013, 06:58 PM
  4. [SOLVED] Code to put today's date in empty cell dependent on selection from drop-down list
    By vickyh1 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-28-2013, 09:09 AM
  5. 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
  6. Display a Value based on Today's Date in Date Range
    By barella in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2010, 06:25 AM
  7. Replies: 3
    Last Post: 02-26-2009, 04:28 PM

Tags for this Thread

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