Solved How can I get two dates to count if there are 2 or more days

November 24, 2015 at 12:54:06
Specs: Windows 7
This is what I have :
Column L Column N
Start Stop
Date/time Date/time
1/1/2014 8:00:00 AM 1/4/2014 07:00:00AM

My problem is that I have to flag and count any start and stop times that goes over 2 days. I have over 12,000 rows to count, so that is way too many to count by hand.


See More: How can I get two dates to count if there are 2 or more days

Report •


✔ Best Answer
November 24, 2015 at 14:46:44
OK, try something like:

With your data looking like:

 
            L           M         N                  O          P
 1) Start                        Stop              
 2) Date                Time     Date                Time
 3) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 4) January 01, 2014   8:00 AM   January 03, 2014   7:00 AM     2
 5) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 6) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 7) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 8) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 9) January 01, 2014   8:00 AM   January 03, 2014   7:00 AM     2
10) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3

In cell P3 enter the formula: =N3-L3
or
In cell P3 enter the formula: =DATEDIF(L3,N3,"D")
Drag down as many rows as necessary.
They will both produce the same result.

Now to count those dates that are greater than 2 days,
in cell P11 enter the formula: =COUNTIF(P3:P10,">2")

Change the range numbers to suit your needs.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
November 24, 2015 at 13:17:50
Sorry, don't understand what it is you want.

Your data is simply two Dates & Times with no reference.

Does not simply doing a subtraction not work?

A bit more of an explanation would help and if you can post a before & after.
If you are going to post data, please read this HOW-TO which explains the
use of the < pre > tags to alight your data.

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
November 24, 2015 at 13:24:17
Somthing like:
       A                B                C
1) Start	      Stop	
2) Date/time	      Date/time	
3) 01/01/2014 08:00   01/04/2014 07:00	2.96

In cell C3 the formula =B3-A3

MIKE

http://www.skeptic.com/


Report •

#3
November 24, 2015 at 14:01:01
My problem is that I have to flag and count any start and stop times that goes over 2 days.

You can use Conditional Formatting to "flag" those dates that are over 2 days.

Try this:


1) Select your cell or Range of Cells, L3 - O99
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =$N1-$L1>2

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK


See how that works for you.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 24, 2015 at 14:29:02
I guess I should have explained myself better.

The data I want at the end is, how many were overdue. It does not matter if they are 1 or 100 days overdue. I have 12 worksheets with an average of 12,000 rows of data to go through. I'm use to working outside and not in the office, but my boss wants to expand my "knowledge." So this was my thought... use an IFstatement, so if they are overdue I would get a "1" in a separate column and then auto sum that column. It seems I can't get past my "thoughts."

Thanks for your help.


Report •

#5
November 24, 2015 at 14:46:44
✔ Best Answer
OK, try something like:

With your data looking like:

 
            L           M         N                  O          P
 1) Start                        Stop              
 2) Date                Time     Date                Time
 3) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 4) January 01, 2014   8:00 AM   January 03, 2014   7:00 AM     2
 5) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 6) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 7) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 8) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3
 9) January 01, 2014   8:00 AM   January 03, 2014   7:00 AM     2
10) January 01, 2014   8:00 AM   January 04, 2014   7:00 AM     3

In cell P3 enter the formula: =N3-L3
or
In cell P3 enter the formula: =DATEDIF(L3,N3,"D")
Drag down as many rows as necessary.
They will both produce the same result.

Now to count those dates that are greater than 2 days,
in cell P11 enter the formula: =COUNTIF(P3:P10,">2")

Change the range numbers to suit your needs.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#6
November 24, 2015 at 15:02:03
Mike,

Thank you! That works great!!!!!!!!!!

Is there a way the highlight the ones that are over 2?

If not, this is awesome.


Report •

#7
November 24, 2015 at 15:32:15

Is there a way the highlight the ones that are over 2?

See my Reply # 3 about Conditional Formatting

MIKE

http://www.skeptic.com/


Report •

Ask Question