Need help with new flow - one of my dates is not showing correctly

[Dougers1]

Hi  i had a similar issue with one of my other flows and was giving a formula to add into the expresiion tab, the formula worked a treat, so i built another flow and thought i could do same but its working for return date  but no leave date

My Flow

 formula for leave date 

formatDateTime(items('Apply_to_each')?['Return date'], 'dd-MM-yyyy')

what do i need to do ?

[ManishSolanki]

Hi @Dougers1 

Did you try changing the column name 'Leave date' in the expression:

formatDateTime(items('Apply_to_each')?['Leave date'], 'dd-MM-yyyy')

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Dougers1]

yeah  changed that to leave date  and still failed - it looks like it not saving  because when i go back to check its blank

[ManishSolanki]

Hi @Dougers1 

Pls share the error details with screenshot if flow breaks. If flow runs successfully, then pls check the field's name from the output of List rows present in a table action in last run instance. Replace the exact field name in the expression shared in the previous reply.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Dougers1]

this is the error i get 

also get emailed this

Flow Run Failure: One or more fields provided is of type 'Null'.
Staffbank Annual leave started experiencing issues at 13:04 ,10/5/2023 UTC

What happened?
One or more fields provided is of type 'Null', a different type is expected.

[wskinnermctc]

@Dougers1 check out my post about Excel dates and blank fields and the solution.

Basically you will have to change the List Rows Present in a Table to Date type Serial and then use an expression like this.

if(empty(item()?['Birth Date']),null,addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['Birth Date']),86400),'0'))))

but you will need to wrap the last addSeconds part in a formatDateTime() since my example was getting an output of DateTime, but you are wanting a string.

if(empty(item()?['Birth Date']),null,formatDateTime(addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['Birth Date']),86400),'0'))),'dd-MM-yyyy'))

[Dougers1]

Sorry im new and dont understand - this worked in my other flow and that was set up the same. when i change the leave date to dynamic

i get this email

Good News David

Your annual leave has been processed, leave requested - 2023-09-21T00:00:00.000Z to 21-09-2023 for 22.3 Hours

Your new balance is now 35 hours

[wskinnermctc]

Yes, if you used all Dynamic Content options in the email, you wouldn't get any errors.

The error was due to a blank date value. The function formatDateTime() was trying to format the blank/null and created an error.

So you will have to adjust the expression so it can deal with a blank value.

And like @ManishSolanki said above, make sure the name is correct in the expression. There is a difference between "Leave date" and "Leave Date" since the "d-D" are different capitalized and lowercase.

[Dougers1]

i have tried this

I added the second formula and changed birth date to Leave date

if(empty(item()?['Birth Date']),null,formatDateTime(addSeconds('1899-12-30',int(formatNumber(mul(float(item()?['Birth Date']),86400),'0'))),'dd-MM-yyyy'))

my email now  comes back as below missing the leave start date

Your annual leave has been processed

Leave Date Start:

Leave Date End: 21-09-2023

Annual leave hours approved 22.3

Your new balance is now 35 hours

[wskinnermctc]

Are there any blank fields in the workbook table?

Also, if you are using my formula, you need to change the dropdown option for DateTime Format to be Serial.

[wskinnermctc]

Can you check the column headings in your workbook for any leading spaces.

From your screenshot it appears that the dynamic content "Leave date" is further right than the dynamic content of Hours Requested.

[Dougers1 · Accepted solution]

It was the spread sheet that had a blank space before Leave date , it now working perfect thank you very          much 🙂