Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

late submissions calculated column

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 CreatedDue DateActual SubmissionSubmitted Late?
12/1/202112/4/202112/2/2021No
12/2/202112/5/202112/6/2021Yes
12/3/202112/6/202112/6/2021No
12/13/202112/16/2021 No
12/14/202112/17/2021 No
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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"

6 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      jcastr02
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

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

       

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    create a calculated column
    subitted late = if(or([Actual Submission]=0,[Actual submission]<=[Due Date]),"Not Late","late")
    • jcastr02's avatar
      jcastr02
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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"