cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PJBruen
Helper III
Helper III

Populating Sharepoint with Excel data (accounting for blank date fields)

Hi, 

I'm building a flow which gets data from a spreadsheet and populates a list in Sharepoint. All is fine, until I come to a blank cell in Excel that needs to populate a date in Sharepoint.

 

Presently the formula being used is: addDays('1899-12-30',int(item()?['In Progress']),'yyyy-MM-dd')), but if the value of the cell is blank then I would like to show that as a blank field in Sharepoint.

 

Can anyone advise the best way to do this? There a 5 columns of dates, so do i need 5 sets of conditions (to ignore the field if blank) or can I simply change the formula above to ignore any action of the cell is blank?

 

This is what I would like to achieve! 

PJBruen_0-1606232001562.png

 

Many thanks.

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

The problem is there is no way to set a date field in SharePoint to be blank.  If you set it to any value at all it will always require a valid date/time.  To leave it blank you need to not fill in that field when updating the record.  So you would need to use a condition to setup parallel branches for when the date is or isn't blank.  Or you can just set the date to a default value like 01/01/1900 which you can then account for when you use the values later.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

3 REPLIES 3
Pstork1
Dual Super User III
Dual Super User III

The problem is there is no way to set a date field in SharePoint to be blank.  If you set it to any value at all it will always require a valid date/time.  To leave it blank you need to not fill in that field when updating the record.  So you would need to use a condition to setup parallel branches for when the date is or isn't blank.  Or you can just set the date to a default value like 01/01/1900 which you can then account for when you use the values later.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

PJBruen
Helper III
Helper III

Many thanks @Pstork1 

Hi @Pstork1 

Sorry, as a follow up to this. I think defaulting the date as you suggested might be a cleaner option. Are you able to advise how i change this formula to do that?

addDays('1899-12-30',int(item()?['In Progress']),'yyyy-MM-dd'))

 

I've tried this, but it just populates Sharepoint with 01/01/1900, regardless:

if(empty(triggerBody()?['In Progress']),'01/01/1900',addDays('1899-12-30',int(item()?['In Progress']),'yyyy-MM-dd'))

 

Many thanks.

Paul

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (55,025)