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

A	F	H	I	J
Job No	Start	Finish	Date Diff	Pass/Fail
132	1/4/19	3/4/19	2	Pass
189	1/4/19		-31128	Work in Progress
245	3/4/19	23/4/19	15	Fail

<tbody>
</tbody>

986	1/4/1	21/4/29	3650	Error - Check

<tbody>
</tbody>

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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"))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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"))))