Calculating if an assignment is late from midday.

[Boogielips]

Hi.

I am trying to use Excel to work out if an assignment is late or not and I'm having a bit of trouble with it. I have the following (example) data:

A1 = Date assignment is due (29/10/2012)
B1 = Time assignment is due (12:00:00)
C1 = Date student submitted (29/10/2012)
D1 = Time student submitted (12:01:00)

E1 = amount of days late (1)

What I am looking for is a formula to put in cell E3 that calculates the number of days late from midday on the submission date, so that if a student submits at 12:01:00 on the submission date (29/10/2012) it shows in cell E3 that it is one day late. If a student submits at the same time on the following date (12:01:00 on 30/10/2013) it shows 2 days late, etc.

Apologies if this question has already been answered in another thread, I have had a good look through and could not see it anywhere.

I'm pretty new to the joys of using formulas in excel so any help anyone can give would be very gratefully received.

Many thanks

[ConneXionLost]

How about this:

Please Login or Register  to view this content.

[Boogielips]

Hi ConneXionLost,

Many thanks for the speedy reply! I have just done a quick test and that seems to work perfectly, you may have saved my sanity!

Thanks for the help, this will be a great time saver.

[Boogielips]

Hi Again,

That formula works like a charm except for an ammendment I need to make. At the moment it is returning some negative numbers and I would very much like those to return a 0 instead.

For example:

A1 = Date assignment is due (12/11/2012)
B1 = Time assignment is due (12:00:00)
C1 = Date Student submitted (31/10/2012)
D1 = Time student submitted (15:34:34)

This returns a result of -11 in cell E where as I would like it to be 0

Another example is if a student has not submitted at all:

A1 = Date assignment is due (12/11/2012)
B1 = Time assignment is due (12:00:00)
C1 = Date Student submitted (0)
D1 = Time student submitted (0)

Then this returns a result of -41255 in cell E and again I would very much like this to be 0 as well.

Basically I want the default to always be 0 unless they are late, is there any way I can do this?

Again your help and patience with an Excel novice is greatly appreciated.

[Boogielips]

Hi again,

Please disregard that last post, I have figured it out.

It was just something stupid that I was doing which, for embarrassment purposes, I decline to go into

Thanks for the original help again.