Conditional Formatting based on empty Date column

%3CLINGO-SUB%20id%3D%22lingo-sub-2897831%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20based%20on%20empty%20Date%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897831%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20SharePoint%20list%20and%20I%20want%20to%20apply%20conditional%20formatting%20if%20an%20item%20hasn't%20been%20submitted%2C%20in%20which%20case%20a%20Date%20field%20will%20be%20empty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20applied%20other%20formatting%20based%20on%20whether%20a%20column%20contains%20data%20by%20simply%20picking%20the%20column%2C%20'is%20equal%20to'%20or%20'is%20not%20equal%20to'%20and%20then%20leave%20the%20last%20field%20blank.%20But%20that%20doesn't%20seem%20to%20work%20with%20a%20Date%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20tried%20to%20put%20the%20condition%20as%20'Column'%20'is%20not%20equal%20to'%20'1%2F1%2F1990'%20-%20in%20the%20hope%20that%20picking%20a%20date%20long%20ago%2C%20nothing%20will%20match%20but%20this%20doesn't%20work%20either.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20other%20conditions%20so%20I%20didn't%20really%20want%20to%20go%20to%20JSON.%20I%20don't%20know%20enough%20to%20work%20out%20how%20to%20put%203%20conditions%20together!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20conditionally%20format%20based%20on%20the%20empty%20Date%20value%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EChristine%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2897831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2906305%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20empty%20Date%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2906305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%26nbsp%3BThanks!%3C%2FP%3E%3CP%3EBut%20that%20only%20formats%20that%20field%20while%20I'm%20trying%20to%20format%20the%20entire%20item.%20As%20a%20work%20around%2C%20I've%20introduced%20a%20Status%20column%20but%20that%20means%20I%20need%20to%20manually%20update%20it%20..%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2F%409839A717402516D64549B98324F4F0C1%2Fimages%2Femoticons%2Fsad_40x40_1.gif%22%20alt%3D%22%3Asad%3A%22%20title%3D%22%3Asad%3A%22%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Amapola67_0-1635710224517.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321977i1F294543C0D61A73%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Amapola67_0-1635710224517.png%22%20alt%3D%22Amapola67_0-1635710224517.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good Morning

 

I have a SharePoint list and I want to apply conditional formatting if an item hasn't been submitted, in which case a Date field will be empty.

 

I have applied other formatting based on whether a column contains data by simply picking the column, 'is equal to' or 'is not equal to' and then leave the last field blank. But that doesn't seem to work with a Date field.

 

I've also tried to put the condition as 'Column' 'is not equal to' '1/1/1990' - in the hope that picking a date long ago, nothing will match but this doesn't work either.

 

I have other conditions so I didn't really want to go to JSON. I don't know enough to work out how to put 3 conditions together!

 

Is there any way to conditionally format based on the empty Date value?

 

Thanks,

Christine

8 Replies

@Amapola67 you can format the date column in advanced mode as follows:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(Number(@currentField) == 0, '#541417', 'green')",
    "color": "white"
  }
}

SP-List.png

 

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

@RobElliott Thanks!

But that only formats that field while I'm trying to format the entire item. As a work around, I've introduced a Status column but that means I need to manually update it .. :sad:

 

Amapola67_0-1635710224517.png

 

@Amapola67 If you want to format entire row based on empty date column, you have to use view formatting.

Example:

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$StartDate], '', 'sp-field-severity--severeWarning')"
}

 

 

 

ganeshsanap_0-1635753998021.png

Similarly, you can apply any of the predefined classes instead of 'sp-field-severity--severeWarning' as given in below links as per your requirements:

  1. Predefined classes
  2. Reusable SharePoint Online Classes for Modern UI

See other ways to check if date column is empty at: SharePoint JSON formatting: Check if date & time column is blank/empty


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

@ganeshsanap 

 

As I have multiple conditional formats, I need to put your code into what was generated. I made the read bit Date is not some date but it generates an awful lot of code compared to your string.  (I know from VBA that a well-structured code is easier to read but JSON seems to go a bit over the top! It's no longer well structured, it's blown up.)

 

And quite frankly, I can't make head or tail of the code.  There's no if that I can see. 

 

Can I incorporate your code string in this?

          {
            "operator": ":",
            "operands": [
              {
                "operator": "==",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      {
                        "operator": "toDateString()",
                        "operands": [
                          {
                            "operator": "Date()",
                            "operands": [
                              "[$EnteredPhoenix]"
                            ]
                          }
                        ]
                      }
                    ]
                  },
                  {
                    "operator": "Date()",
                    "operands": [
                      {
                        "operator": "toDateString()",
                        "operands": [
                          {
                            "operator": "Date()",
                            "operands": [
                              "Sun Jan 01 2012"
                            ]
                          }
                        ]
                      }
                    ]
                  }
                ]
              },
              "sp-css-backgroundColor-BgPeach sp-css-color-PeachFont",

 

@Amapola67 "But that only formats that field while I'm trying to format the entire item" - it would have been helpful if you'd said that in your original post!

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

 

 

@RobElliott 

Sorry, must be my wording. I have been trying to avoid learning SharePoint .. I thought 'item' was an entry, i.e. the line. Vs. column which is a field?

 

So when I said I'm trying to format the item, I meant I'm trying to format the line. Trying to speak SharePoint and obviously failing. 

No need to apologise, I sounded irritated which I didn't mean to but one of our staff had done something incredibly stupid on one of our sites just before I posted.

Rob

@RobElliott Good staff is so hard to find these days :smile:

I'm learning more about SharePoint these days than I ever wanted to know!!

 

Have a good day!

Christine