Can't make a formula that allows for a static date.

[fantabulator]

Hello all,

I am a teacher and am finding that tracking late work, which is more and more common, is getting to be more challenging. I am trying to create a solution in excel. My current thought process is to use checkboxes for each assignment and attach a date to the "true" return of each box. The problem is that using a date formula results in a dynamic process that updates each time I open the sheet, and I want the precise date the assignment was submitted to be static from the time I click the box.

I've checked the net for answers but am having problems making heads or tails of what is out there.

I have attached the very rudimentary beginnings of my concept. Thanks in advance for any suggestions you may have.

 

[Somendra Misra]

Hi,

How about entering the date of submission manually and than comparing each date with the standard date of submission for each assignment to point whether the submission was in time or not?

The same can be done through formula, and later we can do things like how many students submitted in time or not?

Regards,

 

[fantabulator]

Hi,

How about entering the date of submission manually and than comparing each date with the standard date of submission for each assignment to point whether the submission was in time or not?

The same can be done through formula, and later we can do things like how many students submitted in time or not?

Regards,

Thanks for your reply. I'm not sure I completely understand your suggestion. Are you suggesting that I input the date manually for each student, each assignment? That would be too time prohibitive to do it this way.

I was hoping to use a checkbox system for this and assigning the default value of each box to true(meaning work is submitted), thus saving a lot of time. The submission date would be at the top of the column and all assignments on time would get that date.(hoping optimistically that more students will submit than not) When false, no date would be assigned until the value was true(I would check the box when the assignment was submitted), and then I would like the date it is submitted to be assigned to that student's assignment.

I would then have another row of checks for the next assignment, and so on and so on, until all of the assignments are tracked.

Maybe it is not possible the way I am doing it...that's why I am appealing those of you with far more experience than I.

Thanks again,

 

[fantabulator]

My thought for differentiating the dates is using an if statement like the one below, but the today dates are dynamic which is where my problem lies:

=IF(D4,IF(TODAY()=$C$3,$C$3,IF(D4,TODAY()," "))," ")

I have added it to the file

 

[Nebu]

I have put a macro let me know if this helps...

 

[David Evans]

If you enter the actual date that a student submit their work in Column G, then your formula in Column H can read =IF(G4<=$C3,"On Time","Late")

An easier way to enter the current date into a cell is to CTRL+; (ctrl and semicolon) it will enter the current date and it will be static, unlike using =Today().

That will be easier than your check boxes/TRUE/FALSE/Macro mechanism, I suggest.

 

[Smallman]

Hi

I think you will need to do this in VBA as any date reference in formula will be a moving feast. I think you will need to test two criteria, has the student submitted their essay and is there already a date in Column G. If you do not test two criteria then the dates will be overwritten days after with a date later than the student submitted.

I would also suggest you ditch your check box method. I think that is a lot of hard work for a simple task. A basic Yes No data validation does the same thing. Then you could use a worksheet change event to fire any time you change the status of one of your students.

Here is an example;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
    If Not Intersect(Target, [C4:C100]) Is Nothing Then
        For i = 4 To Range("C65536").End(xlUp).Row
            If Range("G" & i) = "" And Range("C" & i) = "Yes" Then Range("G" & i) = Date
        Next i
    End If
End Sub

Have called the sheet example. Will upload a file to show workings.

Take care

Smallman

 

[fantabulator]

Thanks everyone for your replies. I decided to stay with the checkboxes, as eventually I want to share the program with other teachers, even those who are afraid of computers. I want the interface to be as simple as possible -- clicking the box whenever a student submits an assignment - records the date, compares it to the due date, compiles all assignment info.

All the user will see is 33 check boxes for approx. 20 assignments on the one page. I have included a working copy if anyone is curious. The data page is the next page, which will eventually be hidden. Then will follow a summary page for each student in the following worksheets (also included)and possibly a summary statistics page for the class.(haven't thought that far ahead yet)

What I found by searching for hours on the net was this looping formula, and it seems to have solved my problem :

This goes in C4

=IF(B4<>"",IF(C4="",TODAY(),C4),"")

You need to set the iterations to max 1. Works like a charm. Check box - date is date stamped for that day.

Thanks again for the suggestions. If interested, I will post some further development later.