cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbsin
Post Prodigy
Post Prodigy

need help - what is the expression for Date and Time in Workflow from Excel.

HI

 

Can I know what is the DateTime in the expression for workflow?  the rest of the item are working except for the datetime field.

 

Please advise.  Thanks

I was following Shane's video

 

I try this but it failed

I need the datetime .. thanks

 

 

 

Thank you

 

 

bbsin_1-1623052937531.png

bbsin_2-1623052966367.png

bbsin_3-1623053249626.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @bbsin :

I've made a test but it worked well on my side:

addSeconds(
  '1899-12-30T00:00:00Z',
  int(formatNumber(mul(float(items('Apply_to_each')['DateBooked']),86400),'0'))
)

v-bofeng-msft_1-1623221407212.png

 

v-bofeng-msft_0-1623221389523.png

 

Best Regards,

Bof

View solution in original post

12 REPLIES 12
v-bofeng-msft
Community Support
Community Support

Hi @bbsin :

Please check this formula:

int(items('Apply_to_each_2')?['DateBooked'])

"items('Apply_to_each_2')?['DateBooked']" must be a text type integer number.

I think this link will help you a lot:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#i...

In addition, SharePoint only accepts dates after 1/1/1900.

Best Regards,

Bof

Hi @v-bofeng-msft 

 

Not too sure about the codings.. will have a look and figure out.. 

So I need to separate the date and time in two different columns?

 

Thanks will try it out.

Hi @bbsin :

I suggest you solve the problem of Int function reporting errors first.

Seems DateBooked is a Date column. If so,

int(items('Apply_to_each_2')?['DateBooked'])

will not work.Could you tell me what do you want to achieve with this formula?

addDays('1899-12-30',int(items('Apply_to_each_2')?['DateBooked']),'yyyy-MM-dd')

 Besides, can you use compose action to output a value of "items('Apply_to_each_2')?['DateBooked']" and show it to me?

Best Regards,

Bof

bbsin
Post Prodigy
Post Prodigy

@v-bofeng-msft 

 

Hi Bof,  I added the date time into the flow. but it's not updating from Excel to SP list and I notice it keep creating as new records?    I edit existing data in Excel.   I want to be able to edit and add new records directly from excel to SP list.. not sure where is not right

 

I saw someone video and add the variables.. but not working well... can help  the flow run success but data and time not pick up the right one and records keep adding as new records?

 

 

t keep add as new records and the date and time didn't update as those in excel.

 

 

 

 

Hi @v-bofeng-msft 

 

I want to be able to use Excel add, edit and delete which SP list will auto update.

 

Yes, There are two columns with date and time that I want to pick up into SP list from Excel

bbsin_0-1623208448725.png

 

am trying to achieve add. edit and delete same this video but my excel doc and records list are all in SP 

 

I change the flow following this video but cannot get it??

Number are all number field so I just float.. not sure if it's right 🙂

bbsin_1-1623209597163.png

 

Thanks

 

 

Hi @bbsin :

Could you tell me what the meaning of "Number are all number field so I just float.. " is?

What is the expression now?What is your target data type? What values did you call? What is their data type?

Best Regards,

Bof

hi @v-bofeng-msft 

 

as the field in SP are number field , not sure if I need to use fx= float?

 

Will this help you to see?

 

 

1.PNG

Hi @bbsin :

I've made a test but it worked well on my side:

addSeconds(
  '1899-12-30T00:00:00Z',
  int(formatNumber(mul(float(items('Apply_to_each')['DateBooked']),86400),'0'))
)

v-bofeng-msft_1-1623221407212.png

 

v-bofeng-msft_0-1623221389523.png

 

Best Regards,

Bof

Hi Bof, @v-bofeng-msft 

 

Thank you.. yes it ok now. great

 

I know why cos I have a ? in .. from the video..  

Will try to build up the rest of the flow 

 

Do I need to use the float for column - number in SP list???

 

Thank you 🙂😘

Hi @bbsin :

No matter whether you add "?" or not, the result will not be affected.

Best Regards,

Bof

Hi @v-bofeng-msft 

 

Thanks.. Cannot understand the code what is meant.. but good that it is working

Question.. For column that is Number field, Do I need to use expression - float()??

bbsin
Post Prodigy
Post Prodigy

Hi  @v-bofeng-msft 

 

How to I get the Parse JSON scheme?

 

bbsin_0-1623237101478.png

 

Thanks

 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,394)