Date Difference Formula

[Asouf]

After great success in my last post, were I received help with Excel, Im once again seeking the help of 'experts'

I have a work log spreadsheet which I need to extract some performance indicators and then present these as monthly graphs.

The jobs have a 3 working day pass/fail trigger and needs to show working in progress jobs (without a finish date) and if possible, an Error state if the datedifference is a negative or above an arbitrary value, maybe 1000 days+ which would show an inputting error...

A F H I J
Job No Date Start Date Finished DateDiff (working days) Pass/Fail
132 01/04/2019 03/04/2019 2 Pass
189 01/04/2019 -31128 Working in Progress ( How can I remove the -31128 from showing)
245 03/04/2019 23/04/2019 15 Fail
986 01/04/2019 21/04/2029 3650 Date Error -Please Review

Ive tried the following, to begin with but any guidance on a better method is greatly appreciated.
=IF(I37>4,"FAIL",IF(I37<=4,"PASS",IF(I37<=0,"Too Few Days")))

Thanks

 

[FormR]

Re: Date Difference Formula Help Req'd

Hi, perhaps something like this..

Excel 2013/2016
	F	G	H	I	J
3	01/04/2019		03/04/2019	2	Pass
4	01/04/2019				In progress
5	03/04/2019		23/04/2019	14	Fail
6	01/04/2019		21/04/2029	2624	Error State
7	01/04/2019		01/03/2019	-23	Error State
Sheet1

Cell Formulas
Range	Formula
I3	=IF(H3="","",NETWORKDAYS(F3,H3)-1)
J3	=IF(I3="","In progress",IF(OR(I3<0,I3>1000),"Error State",IF(I3<=4,"Pass","Fail")))

 

[Asouf]

Re: Date Difference Formula Help Req'd

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Date Diff[/TD]
[TD]Pass/Fail[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]1/4/19[/TD]
[TD]3/4/19[/TD]
[TD]2[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]189[/TD]
[TD]1/4/19[/TD]
[TD][/TD]
[TD]-31128[/TD]
[TD]Work in Progress[/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD]3/4/19[/TD]
[TD]23/4/19[/TD]
[TD]15[/TD]
[TD]Fail[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]986 [/TD]
[TD]1/4/1[/TD]
[TD]21/4/29[/TD]
[TD]3650 [/TD]
[TD]Error - Check[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Asouf]

Re: Date Difference Formula Help Req'd

Hi. Thanks for the prompt reply, however..

The formula for Column I works ok (its going to be hidden so fact it shows the 30000+ days isnt an issue)

The formula for Column J works showing the working day pass/fail, but shows 'Error State' for jobs shown in Column I as 'In Progress'

Hi, perhaps something like this..

Excel 2013/2016

	F	G	H	I	J
	In progress
Fail
Error State
Error State

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]03/04/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Pass[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]03/04/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]23/04/2019[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]21/04/2029[/TD]
[TD="align: right"]2624[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]-23[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=IF(H3="","",NETWORKDAYS(F3,H3)-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(I3="","In progress",IF(OR(I3<0,I3>1000),"Error State",IF(I3<=4,"Pass","Fail")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Re: Date Difference Formula Help Req'd

The formula for Column J works showing the working day pass/fail, but shows 'Error State' for jobs shown in Column I as 'In Progress'

Let try testing column H directly:

=IF(H3="","In progress",IF(OR(I3<0,I3>1000),"Error State",IF(I3<=4,"Pass","Fail")))

 

[Asouf]

Re: Date Difference Formula Help Req'd

Let try testing column H directly:

=IF(H3="","In progress",IF(OR(I3<0,I3>1000),"Error State",IF(I3<=4,"Pass","Fail")))

Many thanks for this.. This works as intended...

Lastly, is there a way of having these formulas work for Column I and J within 'dragging & dropping' the formula down the entire column. As at present there are only 350 job rows, but this is likely to goto 5000+ by year end and the spreadsheet shows either 0 in Col I for rows 351-5000 and 'In Progress' for rows 351 -5000 even though there is no other data on these rows only the copied formulas..

 

[FormR]

Re: Date Difference Formula Help Req'd

You could try this:

=IF(F3="","",IF(H3="","In progress",IF(OR(I3<0,I3>1000),"Error State",IF(I3<=4,"Pass","Fail"))))