late submissions calculated column

[jcastr02]

I have three columns

Date Record was Created

Due Date for Record (Created+3 Days)

Actual Submission Date

I’d like to create a calculated column that reads if the record was submitted late or not (Yes/No)-  What is happening now is when the actual submission date is blank (because they are still within the 3 days, it is showing late)

Any help would be appreciated

Ex. Below

Date Created	Due Date	Actual Submission	Submitted Late?
12/1/2021	12/4/2021	12/2/2021	No
12/2/2021	12/5/2021	12/6/2021	Yes
12/3/2021	12/6/2021	12/6/2021	No
12/13/2021	12/16/2021		No
12/14/2021	12/17/2021		No

[Anonymous]

create a calculated column

subitted late = if(or([Actual Submission]=0,[Actual submission]<=[Due Date]),"Not Late","late")

[jcastr02]

@Anonymous  Thanks so much that worked, one more ?    How could I get ones that are blank with acknowledge Submission date - if those are blank for the output to be "N/A"

[Anonymous · Accepted solution]

you can't modify that column to text, as is in date format.

but what i can do is this

submitted late = if([Actual Submission]=0,"pending",if([Actual submission]<=[Due Date]),"Not Late","late"))

so the blank date will show "pending" instead of "late" nor "not late"

[Samarth_18]

HI @jcastr02 ,

You can create a column using below code:-

Submitted Late? = 
IF (
    Submission_data[Actual Submission] = BLANK ()
        && Submission_data[Due Date] >= TODAY (),
    "No",
    IF (
        Submission_data[Due Date] >= Submission_data[Actual Submission],
        "No",
        "Yes"
    )
)

Output:-

Thanks

[jcastr02]

@Samarth_18  Thank you....How could I get if the acknowledge submission date is blank to show "n/a"  

[Samarth_18]

You can do it but you have to change your date column type to text then replace blank with NA in a query editor, but if you do this then you will lose the purpose of date column.
Usually in such scenario blank is replace with some default date like 01/01/1990.