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

[bbsin]

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

[v-bofeng-msft]

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

[bbsin]

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.

[v-bofeng-msft]

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]

@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.

[bbsin]

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

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 🙂

Thanks

[v-bofeng-msft]

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

[bbsin]

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?

[v-bofeng-msft · Accepted solution]

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'))
)

Best Regards,

Bof

[bbsin]

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 🙂😘

[v-bofeng-msft]

Hi @bbsin :

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

Best Regards,

Bof

[bbsin]

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]

Hi  @v-bofeng-msft 

How to I get the Parse JSON scheme?

Thanks