Calculating the difference between cells, but using the value of another cell if the

[Barking_Mad]

I have the following on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.

A1= User entered value : a fixed submission date that a piece of work is due to be handed in on.
A2 = User entered value : The date entered if work is handed in on time or early
A3 = User entered value : The date the work was handed in if late
A4 = User entered value : The date the piece of work is due to be handed in if an extension has been granted
A5 = Calculated value : How many days late a piece of work is

Essentially I want the calculation in A5 to reflect how many days late a piece of work is. SUM(A3-A1) would do this of course. If the work is early or on time, A2 is ignored.

Sometimes however students have permission to hand work in late, in which case another calculation is needed to work out how many (if any) days over their extension date (A4) their work is. How do I combine these two calculations into the same cell, so that if A3 is empty it checks A4 to see if the student has an extension date and then returns a 'days late' value if applicable?

I hope this makes sense.....

[NBVC]

Does this do it?

=IF(A4<>"",A4-A1,IF(A3<>"",A3-A1,""))

[Barking_Mad]

actually it might - but ive made a small but important oversight - ill get back to this when i get home.....

cheers

[Barking_Mad]

ok if anyone has time, here's the amended question as i realised the first one wasn't quite right.

I have the following informaiton on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.

A1 = User entered value : The date work is originally due to be submitted.
A2 = User entered value : The date work was actually submitted (without being granted an extension).
A3 = User entered value : The date work is to be submitted if an extension has been given.
A4 = Calculation showing number of days late : If there is a value in A2 (but not one in A3) then subtract A1 from A2. But if there is a value in A3 then subtract value of A3 from A2 instead.

I also need it to say, 'days late' after any instance where the value returned in A4 is positive. If the value calculated is not >0 then A4 needs to be left entirely blank.

Any help would be much appreciated!

[NBVC]

Try:

=IF(A3<>"",A3-A1&" days late",IF(A2<>"",A2-A1& " days late",""))

[Barking_Mad]

Thanks, i noticed that two cell references were the wrong way round. This seems to work.

=IF(B3<>"",B2-B3&" days late",IF(B2<>"",B2-B1& " days late",""))

Is there any way i can get it to leave the entire cell blank when it calculates it to be '0 days late' or if it's early?

Thanks again.

[NBVC]

Try

Please Login or Register  to view this content.

[GuruWannaB]

Try this...just more IF's nested

Please Login or Register  to view this content.

HTH,

-GWB

[Barking_Mad]

thanks both of those are great. Much appreciated.

[NBVC]

Does GWB's handle your negatives as you desired?