Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
May 7th | Register Today: Understanding Agents- Introductory Session
🌸May 8th | You're Invited: Community Spring Festival Awards Celebration🌸
May 14th | Power Platform Community AMA: Power Apps Edition
News and Announcements icon
Community site session details

Community site session details

Session Id : SOPpHD58GjJ7x+0OzazxBl
Power Platform Community / Forums / Power Automate - General Discussion / Power Automate - Handl...
Power Automate - General Discussion
Answered

Power Automate - Handling Blank Dates

Like (0) ShareShare
ReportReport
Posted on 19 Oct 2023 04:37:32 by glitter1234567 125

Hi All,

 

I am trying to populate (create or update) a sharepoint list from an excel file.  This file has dates which I am converting as follows 

addDays('1899-12-30',int(items('Apply_to_each')?['Date Return']),'yyyy-MM-dd')
 
However I seem to be running into issues when the date is blank in excel - how to I update this formaul to populate NULL in my SharePoint list if the 'Date Return' is NULL?
 
Thanks in advance for your help
  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 41,832 Super User 2025 Season 1 on 20 Oct 2023 at 00:23:34
    Re: Power Automate - Handling Blank Dates

    @glitter1234567 

    Oh i see the problem glitter so sorry, we are just missing each other in communication one second

     

  • Verified answer
    glitter1234567 Profile Picture
    glitter1234567 125 on 20 Oct 2023 at 00:19:42
    Re: Power Automate - Handling Blank Dates

    I got it to work with this equation:

    if( less ( length ( items('Apply_to_each')?['Date Return'] ) ,1 ) , null, items('Apply_to_each')?['Date Return'] )
     
    Thanks so much for your help 🙂
  • glitter1234567 Profile Picture
    glitter1234567 125 on 20 Oct 2023 at 00:08:46
    Re: Power Automate - Handling Blank Dates

    Thanks Mike - that didn't work either with the same error 😞

     

    Here are the inputs for my create item:

    {
        "host": {
            "connectionReferenceName": "shared_sharepointonline",
            "operationId": "PostItem"
        },
        "parameters": {
            "table": "2a993d02-c0c0-4679-bf4d-3e74473b0438",
            "item/Title": "130524",
            "item/Title0": "Dr",
            "item/First_x0020_Name": "First Name",
            "item/Surname": "Surname",
            "item/Full_x0020_Name": "First Name Surname",
            "item/Date_x0020_Return": ""
        }
    }

     

    Then I used your updated formula

     

    coalesce(items('Apply_to_each')?['Date Return'], null)

     

     

     

    I double checked my Sharepoint column and it's just standard date/time nothing unusual I don't think.

  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 41,832 Super User 2025 Season 1 on 19 Oct 2023 at 23:59:33
    Re: Power Automate - Handling Blank Dates

    No worries @glitter1234567 


    I accidentally typed it wrong for you, so please try to updated formula, If that does not work I will wrap that in a blank / empty check. I just dont want to add more than is necessary.


    If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

    Cheers

    Thank You
    Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
    https://gernaeysoftware.com
    LinkedIn: https://www.linkedin.com/in/michaelgernaey

  • glitter1234567 Profile Picture
    glitter1234567 125 on 19 Oct 2023 at 23:58:11
    Re: Power Automate - Handling Blank Dates

    My apologies - I am new to Power Automate so high chance I have something else wrong!

     

    So this is the output from my List rows present in a table for the row that is failing (I have numerous dates in the dataset):

     

     

    "Date 1": "2022-06-25T00:00:00.000Z",
     "Date Return": "",
     "Review": "2024-03-01T00:00:00.000Z",
    "End": "2023-09-25T00:00:00.000Z"

     

    Then I am using a 'Create item' 

     

    Coalesce(items('Apply_to_each')?['Date Return'], addDays('1899-12-30',int(items('Apply_to_each')?['Date Return']),'yyyy-MM-dd'), null) 
     
    This works if there is a value in the date fields - but if there is no value it returns the error above.
     
    Thanks for your help 🙂
     

     

     

  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 41,832 Super User 2025 Season 1 on 19 Oct 2023 at 23:50:01
    Re: Power Automate - Handling Blank Dates

    Hi @glitter1234567 

     

    Hi glitter, sorry I believe I fat fingered for you but I would still like to see the actual date value so try this please, sorry, always doing too may things at once.

     

    coalesce(items('Apply_to_each')?['Date Return'], null)

     

    Also we may need to check if its blank also so please let me know if this works for you, sorry about that I was in a rush and needed to slow down.


    If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

    Cheers

    Thank You
    Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
    https://gernaeysoftware.com
    LinkedIn: https://www.linkedin.com/in/michaelgernaey

  • glitter1234567 Profile Picture
    glitter1234567 125 on 19 Oct 2023 at 23:46:31
    Re: Power Automate - Handling Blank Dates

    Thanks @FLMike for taking the time to respond, unfortunately that expression didn't work for me with the error once it hit the rows where the date is blank 😞

    OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Date_x0020_Return' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'.

     

     

  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 41,832 Super User 2025 Season 1 on 19 Oct 2023 at 06:02:38
    Re: Power Automate - Handling Blank Dates

    HI @glitter1234567 

     

    In your Input box for the Date you would do this. (there are multiple ways this is one)

    Use the Expression tab

    It checks to see if the Date Return is null and if its NOT then it does your addDays, if it is it just uses the null (expression)

    Coalesce(items('Apply_to_each')?['Date Return'], addDays('1899-12-30',int(items('Apply_to_each')?['Date Return']),'yyyy-MM-dd'), null)


    If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

    Cheers

    Thank You
    Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
    https://gernaeysoftware.com
    LinkedIn: https://www.linkedin.com/in/michaelgernaey

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities - Lets Get Started!
May 7th | Register Today: Understanding Agents- Introductory Session
🌸May 8th | You're Invited: Community Spring Festival Awards Celebration🌸
May 14th | Power Platform Community AMA: Power Apps Edition

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - General Discussion

#1
ankit_singhal Profile Picture

ankit_singhal 5 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 2 Super User 2025 Season 1

#2
Mitchell GC Profile Picture

Mitchell GC 2

Overall leaderboard

Featured topics

How to Ask for Help with Your Power Automate Workflow

Product updates

Microsoft Power Platform Community release plans