+ Reply to Thread
Results 1 to 17 of 17

Conditional Formatting with Date/Time with Date/Time of another cell

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Conditional Formatting with Date/Time with Date/Time of another cell

    This isn't my first battle with conditional formatting and I can usually muster my way through. However after googling for 4 hours, and trying everything I can think of I am stuck.

    Supplier PO Scheduled Date/Time Arrival Date/Time Unload Date/Time
    Potato Services 1416-212 2/15/17 6:00PM 2/15/17 6:30PM 2/15/17 8:30PM


    This is a sample of what I am working on. The scheduled date and time are my constant manually input. What I am trying to do is if the arrival time is anytime after the scheduled delivery make it red.
    Then based off the delivery date and time I need the unload time to be in orange if its longer than 2 hours later. I could probably figure one out if I had the other, but I am completely stumped. Any idea
    to go about this?

    TIA

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Find the atthachment

    First condition: =D2>C2
    Second condition: =(E2-D2)>2/24
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Try ....

    For Arrival time

    CF rule

    =D2>C2

    Format fill as RED

    For Unload Time

    Cf rule

    =$E2>$D2+TIME(2,0,0)

    Fill Orange

  4. #4
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    FOr the love of GOD and everything holy THANK YOU, but how do I switch it to make the arrival time red not the scheduled? NVM I got it.

  5. #5
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    I must have had something typed in wrong... I tried a similar formula earlier. Let me ask this would there be a way that for each time it colors to get it to total out to another box? or like just keep track? I have done this before where each sheet is a month and I keep a monthly total on, then bring it all back to a YTD. I can handle gettting from sheet to sheet, Just curious now how I would make it total out seperate which was unload and which was arrival late.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Put the CF formula in another cell and set a flag

    say in G2

    =IF(D2>C2,"Late Arrival","")

    Using Zbor's formula

    in H2

    =IF((E2-D2)>2/24,"Unload Late","")

    You could use COUNTIF to count number of Late Arrivals/Unloads

  7. #7
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Quote Originally Posted by JohnTopley View Post
    Put the CF formula in another cell and set a flag

    say in G2

    =IF(D2>C2,"Late Arrival","")

    Using Zbor's formula

    in H2

    =IF((E2-D2)>2/24,"Unload Late","")

    You could use COUNTIF to count number of Late Arrivals/Unloads
    Not sure I follow on the last one, and I just tried on the first one, it just made a box that said late arrival. or are you saying do that and then total some other way?

  8. #8
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Actually maybe I do. have the two columns after everything else.

    then I would have =Countif(H2:H800,"Late Arrival")

    But I would have to do the Formulas above all the way down the sheet for each one correct? We see 400+ trucks most months.
    Last edited by jnepsa; 02-17-2017 at 04:07 PM.

  9. #9
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Some how I have again got this messed up. I got the late arrivals added to all cells, used the same thing to do it to late unload and it wont copy down the sheet. Well it wont color them.

    I was trying to attach the book but it wont let me. They only thing I changed is instead of applying to $E$2 I changed it like I did the arrivals to apply to $E:$E

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    How to:

    Attach a sample workbook. (Not a picture!)
    Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
    Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
    Remember to desensitize the data.
    Note:
    • Please do not attach password protected workbooks/worksheets
    • Please do not attach file(s) from exterior servers
    • Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

    20 rows of data is enough (probably)

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  11. #11
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41
    That was how I tried, for some reason the dialog wouldn't open



    QUOTE=sandy666;4585767]How to:

    Attach a sample workbook. (Not a picture!)
    Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
    Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
    Remember to desensitize the data.
    Note:
    • Please do not attach password protected workbooks/worksheets
    • Please do not attach file(s) from exterior servers
    • Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

    20 rows of data is enough (probably)

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.[/QUOTE]

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Try turn off , if exist, AdBlock, NoScript and any other blocker
    or
    on Manage Attachments right click and Open In New Tab
    Last edited by sandy666; 02-17-2017 at 05:23 PM.

  13. #13
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Ok trying this again. I don't know what is wrong but the two not in yellow in column E should be yellow as they are over 2 hours from arrival.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    If a mod hops in here, can you delete my second question. I will make that a separate thread after I get this one figured out. Sorry Not 100% how to delete them.

  15. #15
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    you mixed format and type date incorrect
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-17-2017
    Location
    canton, ohio
    MS-Off Ver
    Office 365
    Posts
    41

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    Yup already got it and marked solved

  17. #17
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Conditional Formatting with Date/Time with Date/Time of another cell

    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you)

    Thanks

+ 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] Conditional Formatting of Cells based off of date and time criteria
    By rhett7660 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-16-2015, 03:14 PM
  2. Conditional Formatting Formula - Date & Time for intervals
    By jamesfxd in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2015, 12:23 AM
  3. Need to separate date/time from a single cell and create a date/time chart
    By slicksilver79 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-19-2014, 09:48 PM
  4. modify date time cell to an excel format date time
    By rileyp in forum Excel General
    Replies: 12
    Last Post: 03-24-2013, 08:39 PM
  5. [SOLVED] Using Conditional Formatting to Highlight Cells When Date/Time is 12 & 18 Hours Later
    By redogue in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-31-2012, 10:53 AM
  6. Replies: 2
    Last Post: 12-27-2011, 04:25 PM
  7. [Solved] Conditional Formatting based on date and time
    By HackerJL in forum Excel General
    Replies: 5
    Last Post: 06-08-2011, 06:58 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