I am building a flow to identify a date in increments of 7 from an Excel using 'Compose' and 'Get a Row' action.
The dates on the excel are formatted in "YYYY-DD-MM format
Although the "Key Value" output shows as "2023-06-05", PA is unable locate the same date in same format on my excel.
Any pointers? Screenshot attached
@Sg44 - Use the below only if you want to convert date into an Integer as Excel stores dates as an Integer.
add(div(sub(ticks(startOfDay(utcNow())),ticks('1900-01-01')),864000000000),2)
45086 converted to date is June 9th, 2023.
Thanks,
Bhavesh N
Like this?
add(div(sub(ticks(startOfDay(formatDateTime(utcNow(),'yyyy-MM-dd'))),ticks('1900-01-01')),864000000000),2)
because currently I am getting the error “ no row was found with Id ‘45086’
Also if I want it in Eastern Time, would I need to include the following?
convertFromUtc(utcNow(),'Eastern Standard Time','yyyy-MM-dd')
Hello @Sg44 ,
Yes, you can use expression "utcNow()"
This will provide an output of todays date and time in UTC.
if you require just the date and exclude the time, you can instead use the below. It will be formatted to the way you desire.
"formatDateTime(utcNow(),'yyyy-MM-dd')"
Thanks,
Bhavesh N
Hi @Sg44
Here is the required expression:
add(div(sub(ticks(startOfDay(utcNow())),ticks('1900-01-01')),864000000000),2)
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
Hello. Is there a function that can be used to get today’s date to put in the expression? So it knows the date automatically.
Hi @ManishSolanki ,
Thank you!, it worked.
Also, I replaced '2023-05-06' with output from Compose which was searching for dates with an increment of '7' from utcNow.
Thanks,
Bhavesh N
Hi @Anonymous
Internally excel stores dates as integer so to search a row we need to search with its corresponding integer value. The expression which I had shared earlier is to convert date value to integer value. The same expression can be used with 'Get a row' action:
add(div(sub(ticks(startOfDay('2023-06-05')),ticks('1900-01-01')),864000000000),2)
Replace the date value ('2023-06-05') in your flow. If you set DateTime Format as 'ISO 8601', you will get the date time in proper format from the output of 'Get a row' action.
If this resolves the problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
Hi @ManishSolanki ,
I am not trying to update a row. I am trying to "Get a row" identify the right row as per the "Date" and retrieve associated data from that row and post as a message in MS teams.
If you see here, the Key Value and date from the error box match. But why is PA throwing the error that '2023-05-06' does not exist in the excel.
Thanks,
Bhavesh N
Hi @Anonymous
Using expression, you need to convert the date value to integer to update a row in excel file.
Pls refer to the below flow:
Expression used for converting to integer:
add(div(sub(ticks(startOfDay('2023-06-05')),ticks('1900-01-01')),864000000000),2)
In the above example, I have hardcoded the date value, pls replace it as per your need.
If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks