Closed Thread
Results 1 to 18 of 18

Highlight Cells Tomorrow Excluding Weekend

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Highlight Cells Tomorrow Excluding Weekend

    I'm Googled out

    I have dates entered manually down the C column and i use the standard highlight rule to color the cells i.e Today. Tomorrow and Yesterday. Now my issue is come Friday in the week i wish to have Mondays date highlighted as Tomorrow so it has excluded the weekend. The standard conditional formatting for when date occurs wont work in this instance and i have tried at least 20 formulas from forums etc.

    What am i doing wrong ? or is there a simpler way? I just wish it to not count any weekends and make Friday 1 and and Monday the day after so it will highlight as if it was tomorrow

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Highlight Cells Tomorrow Excluding Weekend

    Here!

    Assuming your data in in cell C1, try this -

    =C1=IF(WEEKDAY(TODAY(),1)=1,TODAY()+1,IF(WEEKDAY(TODAY(),1)=7,TODAY()+2))

    Change the CF range as required.
    Cheers!
    Deep Dave

  3. #3
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    Hey There,

    You can use the following function to do conditional formatting if tomorrow is not Saturday or Sunday.

    =IF(TODAY()+1=WORKDAY(C1,0,2),TODAY()+1,"")
    Last edited by adhawan06; 04-13-2015 at 12:56 AM.
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  4. #4
    Registered User
    Join Date
    02-27-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Highlight Cells Tomorrow Excluding Weekend

    Will this cover the complete C column ?

    Example

    DATE REQUIRED
    16-Apr
    20-Apr
    18-May
    11-May
    07-May
    04-May
    01-May
    07-Jul
    16-Apr
    03-May
    06-May
    17-Apr
    24-Apr
    20-Apr
    11-May
    14-May
    19-Apr
    20-Apr

  5. #5
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    Yes, It will cover the entire column but will only highlight the tomorrow's date if tomorrow is weekdays.

    I hope this is what you were looking for.

  6. #6
    Registered User
    Join Date
    02-27-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Highlight Cells Tomorrow Excluding Weekend

    o.k sorry i really need to explain my self a little better. that dates run from C6 Down and i need it to highlight only the ones for tomorrow's date but of coarse excluding any weekends. I.e Friday 12th and the next date that would need to highlight for tomorrow would be Monday the 15th.

    I really appreciate your help guys it's been doing my head in hahah

  7. #7
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    Hi...You just need to change the date and put it in conditional formatting.

    Go to Cell C6 and and open Conditional Formatting window and follow the below steps

    1. Your cursor should be on C6
    2. Go to Home-->Styles-->Conditional Formatting-->Highlight Cell Rules-->More Rules-->Use a Formula to determine which cells to format
    3. Type the following formula - "=IF(TODAY()+1=WORKDAY(C6,0,2),TODAY()+1,"")"
    4. Click on Okay -->Apply
    5. Copy C6 and drag it to the last cell of C column where date exists.


    It will highlight only tomorrow's date if tomorrow's is not Saturday or Sunday.
    Last edited by adhawan06; 04-13-2015 at 12:56 AM. Reason: Missed +1 in the formula

  8. #8
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    I am also attaching a workbook for your reference. Please see the attachmnet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-27-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Highlight Cells Tomorrow Excluding Weekend

    ok getting there and looking good. have a look at this and see if you can follow. The macro creates a new line on send etc.
    The usual setting today / tomorrow and yesterday (basic ones ) work fine. The dates highlight accordingly. you could image if i had a due date of the 13th
    of April on there it would nit highlight the cell until the 12th (Sunday)
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    Your file is password protected and I have given exactly the same which you are looking for here.

    Let me explain you what function is doing.

    =IF(TODAY()+1=WORKDAY(C6,0,2),TODAY()+1,"")

    So the If condition, If Tomorrow (Today is 13th and tomorrow is 14th Apr) is equal to Workday (Which will be from Monday to Friday) then it will highlight the cell containing date of 14th April'15 else it will not highlight anything and leave it as blank.

    I hope it is very simple what you just need to do is copy the same function and paste it in your file in conditional formatting.

    Cheers!!!
    Anil Dhawan

  11. #11
    Registered User
    Join Date
    02-27-2013
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Highlight Cells Tomorrow Excluding Weekend

    Thank You ill give it a run

  12. #12
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    I hope it will work!!!



    Kindly take few seconds to mark this thread as SOLVED and *Add Reputation if I helped you.

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Highlight Cells Tomorrow Excluding Weekend

    Quote Originally Posted by adhawan06 View Post
    You can use the following function to do conditional formatting if tomorrow is not Saturday or Sunday.

    =IF(TODAY()+1=WORKDAY(C1,0,2),TODAY()+1,"")
    But doesn't this need to work if tomorrow is a Saturday? In that case it needs to highlight the Monday.

    You simply want to format C6 if it's equal to the following working day, which you can do like this:

    =C6=WORKDAY(TODAY(),1)
    Audere est facere

  14. #14
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Highlight Cells Tomorrow Excluding Weekend

    Quote Originally Posted by daddylonglegs View Post
    But doesn't this need to work if tomorrow is a Saturday? In that case it needs to highlight the Monday.

    You simply want to format C6 if it's equal to the following working day, which you can do like this:

    =C6=WORKDAY(TODAY(),1)
    Hi, As user wants if tomorrow is Weekend (Saturday or Sunday) then it should not be highlighted and if the next working day is Monday, It should not highlighted until Sunday So it should be highlighted once i reach on Sunday.

    Quote Originally Posted by d3fcon View Post
    ok getting there and looking good. have a look at this and see if you can follow. The macro creates a new line on send etc.
    The usual setting today / tomorrow and yesterday (basic ones ) work fine. The dates highlight accordingly. you could image if i had a due date of the 13th
    of April on there it would nit highlight the cell until the 12th (Sunday)

  15. #15
    Forum Contributor
    Join Date
    03-11-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    379

    Re: Highlight Cells Tomorrow Excluding Weekend

    Hi d3fcon,

    I too have a similar question & when I test the formulas provided by experts I find them not working.
    I checked the question by OP from post# 6 on-wards.

    The file is attached.

    Note: The formula use Today function. To test the formulas you need to change the System Dates.

    Regards,
    AM
    Last edited by ashishmehra2010; 06-24-2015 at 07:43 AM.

  16. #16
    Registered User
    Join Date
    10-22-2021
    Location
    Palm Springs, CA
    MS-Off Ver
    Version 2019 (Office 365)
    Posts
    1

    Thumbs up Re: Highlight Cells Tomorrow Excluding Weekend

    Quote Originally Posted by daddylonglegs View Post
    But doesn't this need to work if tomorrow is a Saturday? In that case it needs to highlight the Monday.

    You simply want to format C6 if it's equal to the following working day, which you can do like this:

    =C6=WORKDAY(TODAY(),1)
    YES! THANK YOU! I've been searching for this formula for almost two hours. All I want is to highlight the next business day, I knew it didn't need to be something complicated, only something I wasn't thinking of. THANK YOU!!!

  17. #17
    Registered User
    Join Date
    11-10-2021
    Location
    HK
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    1

    Re: Highlight Cells Tomorrow Excluding Weekend

    Hi...still not figure out how it works...could you please share your excel for reference?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,110

    Re: Highlight Cells Tomorrow Excluding Weekend

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excluding weekend hours from time
    By flapface in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2014, 12:49 PM
  2. [SOLVED] VBA code for excluding weekend dates from Web import into Excel
    By vk2013 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-29-2013, 04:15 PM
  3. Replies: 3
    Last Post: 11-15-2012, 07:55 PM
  4. highlight in red if over 3 days excluding weekend
    By unley in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-21-2010, 11:48 PM
  5. Due date excluding weekend days
    By Jfilbig in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-05-2006, 02:50 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.09455 seconds
  • Memory Usage 9,380KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (4)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (18)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (18)postbit_legacy
  • (18)postbit_onlinestatus
  • (18)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1