formatDateTime fails when SharePoint Date column is null while matching Month-Day with today

(3) Share

Report

Posted on by oyepanky

537

Hi Team,

I am working on a Power Automate flow where I need to send notifications based on employees' Date of Joining (DOJ).

My requirement is to compare only the month and day of the DOJ with today’s date (ignoring the year), like an anniversary reminder.

I am using a SharePoint list where the DOJ column is a Date type, and I am looping through items using Apply to each.

What I tried:

In Condition / Compose, I used:

formatDateTime(item()?['DOJ'],'MM-dd')

and compared it with:

formatDateTime(addHours(utcNow(),5.5),'MM-dd')

Issue:

The flow fails with the following error:

InvalidTemplate. The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

What I understand:

It seems some records in SharePoint have a null/empty DOJ, which is causing the failure.

What I tried to fix:

I also tried using:

formatDateTime(coalesce(item()?['DOJ'],'1900-01-01'),'MM-dd')

But I am still facing issues in some cases.

Any help would be greatly appreciated.

Thanks in advance!
Pankaj Jangid
Youtube @oyepanky

Categories:

General topics

Using flows

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Suggested answer

Pstork1 69,556 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

You can solve that problem by putting the item()['DOJ'] value inside a Coalesce() function and providing a valid constant date for use if its null. If you want to use a static date make sure you format it as an ISO 8601 value. Here's an example using today's date. Like this:

formatDateTime(coalesce(item()?['DOJ'],utcNow()),'MM-dd')

formatDateTime(coalesce(item()?['DOJ'],'1900-01-01T00:00:00Z'),'MM-dd')

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
oyepanky 537 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @Pstork1
Thank you for your response and suggestion.

I implemented your recommendation using coalesce() as shown below:

formatDateTime(coalesce(item()?['DOJ'],'1900-01-01T00:00:00Z'),'MM-dd')

This resolved the null error issue, and now the flow is running successfully without failures.

However, I am still facing a problem where the condition evaluates to FALSE, even when the DOJ in SharePoint clearly matches today’s date (same month and day).

For example:

DOJ in SharePoint: 3/27/2025

Today’s date: 03-27

Both values appear identical when formatted, but the condition still does not return TRUE.

Appreciate your guidance on this.

Thanks again!
Pankaj Jangid

Was this reply helpful? Yes No
Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @yepanky

Please try this:

equals( formatDateTime(convertTimeZone(item()?['DOJ'],'UTC','Asia/India'),'MM-dd'), formatDateTime(convertTimeZone(utcNow(),'UTC','Asia/India'),'MM-dd') )

Please change "Asia/India" based on you timezone where SP is launced.

SP stores DateTime fields with both date and time. Even if the list shows “3/27/2025”, under the hood it might be 2025-03-27T00:00:00Z or 2025-03-27T05:30:00Z depending on your site’s timezone. When you format it, the month/day looks right, but the raw comparison still sees a mismatch. That's why convertTimeZone is needed.

Bit - safer one:

and( not(empty(item()?['DOJ'])), equals( formatDateTime( convertTimeZone(item()?['DOJ'],'UTC','Asia/Dhaka'), 'MM-dd' ), formatDateTime( convertTimeZone(utcNow(),'UTC','Asia/Dhaka'), 'MM-dd' ) ) )

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
oyepanky 537 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Haque
Thank you for your detailed explanation.

I tried the suggested approach using convertTimeZone() as well as the safer version with and() and empty() checks. I also updated the timezone to match my SharePoint regional setting (India Standard Time).

However, the issue is still not resolved.

The flow runs successfully without errors, but the condition continues to evaluate to FALSE, even when the DOJ clearly matches today’s date (same month and day). For example:

DOJ in SharePoint: 3/27/2025

Today’s date: 03-27

I also verified the values using Compose actions, and both sides appear identical (MM-dd format), but the condition still does not return TRUE.

At this point, I am unsure if this is due to how Power Automate internally handles DateTime values or if something else is being missed.

Could you please suggest what else I should check or if there is any alternative reliable method to compare only the month and day from a SharePoint Date column?

Thanks again for your support.

Was this reply helpful? Yes No
Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @oyepanky,

You see both sides formatted as MM-dd and they look identical, yet the condition still evaluates to FALSE. That usually means the values are not truly equal at runtime, even though the Compose outputs appear the same. Let's make both side string:

equals( string(formatDateTime(convertTimeZone(item()?['DOJ'],'UTC','Asia/India'),'MM-dd')), string(formatDateTime(convertTimeZone(utcNow(),'UTC','Asia/India'),'MM-dd')) )

Please make sure "Asia/India" is correct or not.

Please try this and let me know.

Was this reply helpful? Yes No
Verified answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @oyepanky,

Looking at your Compose screenshots, the DOJ side is outputting 01-01 which is the coalesce fallback. This means Power Automate is reading the DOJ field as null even though SharePoint shows a value.

The timezone and string conversion fixes will not help here because the field is not being picked up at all.

The issue is almost certainly the SharePoint internal column name.

The display name is DOJ but the internal name used by Power Automate may be different.

To find the correct internal name:
1. Go to your SharePoint list
2. Open List Settings
3. Click on the DOJ column
4. Look at the URL in your browser and find the Field= parameter at the end
5. That value is the internal name

Then in your flow use item()?['InternalName'] replacing InternalName with whatever you find in the URL.

Once the field is returning the correct value, your original formula will work:

formatDateTime(coalesce(item()?['InternalName'],'1900-01-01T00:00:00Z'),'MM-dd')

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

Was this reply helpful? Yes No