Expression to support both blank AND not blank date fields from excel

(0) Share

Report

Posted on by sophieclark

108

Hello,

I'm having major issues with getting my flow to support blank fields and date fields from excel.

I have an excel document that is displaying date fields but in some cases they're optional, meaning the field could be blank. I need to send these dates in an email and either show the date if its there, or leave it blank/display 'N/A' if there isn't a date.

I've searched and searched for this answer, have tried various expressions including addDays, formatdatetime, tried adding in compose functions etc and none of them are supporting both blank and not blank date fields.

This is the expression i'm using so far which works perfectly for when the date is blank:

if(empty(outputs('Get_a_row_3')?['body/What is the expiry date of RTW?']), null, formatDateTime(outputs('Get_a_row_3')?['body/What is the expiry date of RTW?'],'dd/MM/yyyy'))

but as soon as i do a test where there's a date in place, it doesn't just format the date thats present... I get the following error instead:

Unable to process template language expressions in action 'NHS_Pass_Email' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '45074' was not valid. The datetime string must match ISO 8601 format.'.

I've also tested by switching the date and time format in my 'get a row 3' block to ISO 8601 and that doesn't work when the date is blank, but the error when the date isn't blank tells me i need to change something here, but then it wont work for both scenarios. Literally pulling my hair out!

I need an expression or a general answer to tell me how to set this up so that it will include a date in the email if present and still work if the date field is blank. Any help is greatly appreciated!

Thanks

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

45074 is the date number equivalent of

5/28/2023 12:00:00 AM

or in ISO8601

'2023-05-28T00:00:00.000Z'

Was this reply helpful? Yes No
sophieclark 108 on at

Like (0)

Report

Hi @lbendlin thanks for your response.

I'm familiar with the number equivalents, but how do i edit the flow to make sure it will recognise a variety of different dates?

This particular error was for the date 28/5 but i'm getting the same error for other dates too so i need to make sure my flow will recognise all dates, but also recognise (and work) if the date field is blank too?

Thanks

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

Test for empty() and provide an appropriate alternative string in that case.

Was this reply helpful? Yes No
sophieclark 108 on at

Like (0)

Report

Hi @lbendlin - thats what i've been doing and exactly where im running in to issues.

I've been using the expression 'if(empty(outputs('Get_a_row_3')?['body/What is the expiry date of RTW?']), null, formatDateTime(outputs('Get_a_row_3')?['body/What is the expiry date of RTW?'],'dd/MM/yyyy'))'

it works perfectly when the date field is blank, but i also need it to work when there is a date there (which will be most of the time) but i get the error when there's a date.

if that doesnt look correct to you, please can you let me know where i might have gone wrong in my expression and confirm the correct one?

Thanks

Was this reply helpful? Yes No