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

[jnepsa]

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

[zbor]

Find the atthachment

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

[JohnTopley]

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

[jnepsa]

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.

[jnepsa]

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.

[JohnTopley]

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

[jnepsa]

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?

[jnepsa]

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.

[jnepsa]

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

[sandy666]

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.

[jnepsa]

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]

[sandy666]

Try turn off , if exist, AdBlock, NoScript and any other blocker
or
on Manage Attachments right click and Open In New Tab

[jnepsa]

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.

[jnepsa]

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.

[sandy666]

you mixed format and type date incorrect

[jnepsa]

Yup already got it and marked solved

[sandy666]

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