cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ks4050kt
Helper II
Helper II

Date Format Change from Serial Number to yyyy-MM-dd with Word Template

I'm developing PowerAutomate to generate PDF file of monthly report for company employee.

The data is stored in Excel table, then import to Word Template, then Word file is converted to PDF.

The date is also imported from Excel file, but the date value is just populated as Serial Number in Word template.

I tried to use "AddDays" formula to calculate the date in "Select" action, but it is not working well.

Can you please advise how to;

  1. extract "date" data with yyyy-MM-dd format from Excel to PowerAutomate?
  2. convert "date" data from serial number to yyyy-MM-dd format in PowerAutomate?
  3. change format of "date" into yyyy-MM-dd in Word Template with date serial number received from PowerAutomate?

 

ks4050kt_0-1595640909866.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ks4050kt,

 

Please append this expression into the Date field:

addDays('1899-12-30',int(item()?['{11_Date of Study}']))

Annotation 2020-07-27 162050.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
anupam8555
Helper V
Helper V

Hello @ks4050kt ,

 

When you use Excel online(Business) it will return date column values in a numbers so you need to convert into relevant date format. Use below formula to get your date . addDays('1899-12-30',int(items('Apply_to_each')?['{YOUR EXCEL COLUMN Name}']))

 

https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Date-Format-Behaviour-Difference-Between-Ex...

 

Thanks

(Please mark it as resolve if it helps you)

@anupam8555, Thank you for your advice, I tried with your formula 

addDays('1899-12-30',int(items('Apply_to_each')?['{11_Date of Study}']))

but error message occurred as following... Anything is incorrect to make the formula?? 

Request to XRM API failed with error: 'Message: Flow client error returned with status code "BadRequest" and details "{"error":{"code":"InvalidOpenApiFlow","message":"Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Select_Array_from_Excel_for_Word' at line '1 and column '2314' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Select_Array_from_Excel_for_Word' to be referenced by 'repeatItems' or 'items' functions.'.'."}}". Code: 0x80060467 InnerError: Type: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]] Message: Flow client error returned with status code "BadRequest" and details "{"error":{"code":"InvalidOpenApiFlow","message":"Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Select_Array_from_Excel_for_Word' at line '1 and column '2314' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Select_Array_from_Excel_for_Word' to be referenced by 'repeatItems' or 'items' functions.'.'."}}". StackTrace: at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Update(Entity entity, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, Boolean checkForOptimisticConcurrency, Dictionary`2 optionalParameters) at Microsoft.Crm.Extensibility.OData.CrmODataExecutionContext.Update(Entity entity, UpdateOption updateOption) at Microsoft.Crm.Extensibility.OData.CrmODataServiceDataProvider.UpdateEdmEntity(CrmODataExecutionContext context, String edmEntityName, String entityKeyValue, EdmEntityObject entityObject) at Microsoft.Crm.Extensibility.OData.EntityController.PatchEntityImplementation(String& entityName, String key, EdmEntityObject entityDelta) at Microsoft.Crm.Extensibility.OData.CrmODataUtilities.<>c__DisplayClass10_0`2.<InvokeActionAndLogMetric>b__0() at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties) at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext() InternalException: '.

 If you know, can you please advice kindly, thanks.

Hello @ks4050kt ,

 

Can you send me screen shots of your flow where you are using DateTime ? 

 

Thanks

@anupam8555 

DateTime is using in "Select" as shown in the attached screen shot;

ks4050kt_0-1595801411000.png

 

Hello @ks4050kt ,

 

You are not using Apply To each (loop) so you need not to use exact formula I have sent. Please use below : 

 

addDays('1899-12-30',int(--YOUR--VARIABLE--))

 

Thanks

 

 

@anupam8555 In "Select" action, I tired to add expression, but dynamic contents of excel is dis appeared... and cannot be selected in a part of expression as below attached GIF.

Do I need to treat this action out of "Select" action?? I tried to add another action in previous of this "Select" then it could convert serial numbers to date format, but I'm not sure how to input that converted data to "Select" action.

Do you have any idea??

2020-07-27_13h29_14.gif

Hi @ks4050kt,

 

Please append this expression into the Date field:

addDays('1899-12-30',int(item()?['{11_Date of Study}']))

Annotation 2020-07-27 162050.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

@v-litu-msft

Finally I got the success result of this matter with your advice.

But one point to be modified from your advice;

formatDateTime(addDays('1899-12-30',int(item()?['11_Date of Study'])),'yyyy-MM-dd')

The expression shall be above without {} blanket for '11_Date of Study', I realized this necessity with first error message which was shown with other selection items (they do not have {} ).

 

So the point of this matter is how to refer the data in case of "Select" action;

item()?['Excel Column Name']

This is the point as @v-litu-msft  advised.

@anupam8555 Thank you for your advice too.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,399)