“Is there a way to update only a single Date and Time SharePoint column with Power Automate without filling all the mandatory fields in the ‘Update item’ action?”
When you update the SharePoint Date and Time column with Power Automate, you’ve got multiple options. One of them is to use the ‘Update item’ action. The action that will force you to fill out again all the mandatory columns just to update a single date. Or you can do an update via HTTP request to update the column directly. But what you might notice is that the HTTP update doesn’t like the ISO date time format. If you use it, you’ll see a green checkmark at the action, but it won’t update the date. ‘You must specify a valid date within the range of 01/01/1900 and 31/12/8900.’
HTTP request to update a Date (and Time) column
Unlike the standard ‘Update item’ action, HTTP request doesn’t accept the ISO formatted date. Instead, it wants the month first, then the day, and year is the last. And the funny part is, it doesn’t care about the separator. You can use any of the formats below, and maybe even more. The important part is to keep the order – day, month, year.
MM/dd/yyyy
MM-dd-yyyy
MM.dd.yyyyUsing the same HTTP request as many times before…
Method: POST
Uri: _api/web/lists/GetByTitle('<ListName>')/items(<ItemID>)/validateUpdateListItem
Body:
{
"formValues":[
{
"FieldName": "<FieldToUpdate>",
"FieldValue": "<ValueToUpdate>"
}
]
}… the Body part might look as below:
{
"formValues":[
{
"FieldName": "DateAndTime",
"FieldValue": "utcNow('MM/dd/yyyy HH:mm')"
}
]
}Note: the whole utcNow(…) part in the example above is an expression.
Such HTTP request will get the result you want to see – no error code, no error message.
Summary
If you use the HTTP request to update a single SharePoint date column with Power Automate, you should always check the request result. If it doesn’t like the input, it won’t fail (unless it’s terribly wrong). The flow will tell you it was successful, but nothing will be updated. And that’s especially true if you update the date column. If you don’t use the right format, e.g. dd/MM/yyyy, your flow can run for a long time until you notice it’s not working.
This is exactly what I need to do, and have struggle to get to work, but I’m still getting a “Input string was not in a correct format.”
My input string, I think is:
_api/web/lists/getbytitle(‘TeamsUsage_2022-01-03_2022-04-02’)/items(‘TodayDate’)/validateUpdateListItem
TodayDate is the internal name of the column and TeamsUsage_2022-01-03_2022-04-02 is the name of the list, so I am at a loss how to format it correctly.
Any ideas?
Sorry, totally a newbie.
Hello Flavio,
in the brackets in the items(…) should be the ID of the item you’re trying to update. The column is specified in the request Body.
Hello,
That’s exactly what I need.
But instead of entering a new value in the column, I just want to update the column value by itself.
How can I do this?
Hello Antony,
if the value should stay the same then why even bother updating the column?
Because there is a calculated column that is updated according to the date of this column that I wanted to update via http.
Hello Antony,
then get the original value (from a trigger or using ‘Get items’, depending on your flow), format it in the correct format: https://tomriha.com/how-to-format-date-in-a-readable-user-friendly-way-in-power-automate/ and update it in the item. But if it’s just to show some number I’d rather use JSON formatting than updating every item every day: https://tomriha.com/calculate-with-todays-date-in-sharepoint-column-without-daily-updates/
These posts on using the HTTP request to update list items are great! I have a question about the time element: is possible to add seconds, or does the format have to be ‘MM/dd/yyyy HH:mm’?
I tried ‘MM/dd/yyyy HH:mm:ss’ but got the “You must specify a valid date format…” error
Hello Tom,
adding :ss is exactly what I’d do, but if the action doesn’t like then I guess it can’t update seconds.
I found that getting the list by ID vs title worked, I kept getting errors saying my list didn’t exist, so someone probably changed the list title at some point.
_api/web/lists/getByID(‘xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx’)/items(ID)/validateUpdateListItem
I am trying to update a date field as empty when there is no value but it is giving an error. Any way I can update an empty value in date field.
Hello Mudassir,
it should work if you send it just “” as the value.