cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lreinhard7
Level 8

Convert Excel date value

Hi,

I get a date value from Excel in the form 43201 as an example.

How can I make a date value out of this number that I can use in a condition to evaluate whether this date is less or greater than the current date?

Would be great if somebody could help.

Best regards,

Ludwig 

9 REPLIES 9
Community Support Team
Community Support Team

Re: Convert Excel date value

Hi @ lreinhard7,

 

 

Do you want to check if the value of date column in the excel table is less or greater than the current date?

I have a date column "starttime" in the excel table, and you could refer to screenshot below to create the flow to check if the value of date column in the excel table is less than the current date.

Capture.PNG

 

 

The expression in the Condition as below:

 

@less(startOfDay(items('Apply_to_each')?['endtime']), startOfDay(utcNow()))

 

The flow would run successfully as below:

Capture.PNG

 

If you would like to check if the value of date column in the excel table is  greater than the current date, you could use the expression in the condition as below:

@greater(startOfDay(items('Apply_to_each')?['endtime']), startOfDay(utcNow()))

You could refer to link below to learn more about the workflow definition language:

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

 

 

Regards,
Alice Zhang

Life2539
Level: Power Up

Re: Convert Excel date value

I tried this using the suggestion

 

startOfDay(body('Get_a_row')?['Date'])
 
to convert a number into a date for inputing and this is the message I got:
 
InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2369': 'In function 'startOfDay', the value provided for date time string '41796' was not valid. The datetime string must match ISO 8601 format.'.
 
So far no luck converting strings/values into dates for flow. 
dngtie
Level: Powered On

Re: Convert Excel date value

Hello, did you ever find a way to fix the way flow was reading in the values? I've got the same issue now with flow interpreteing cells that are dates as integers.

lreinhard7
Level 8

Re: Convert Excel date value

Hi, For me the following expression worked: formatDateTime(adddays('12-30-1899',int(outputs('Compose_(hold_date_from_Excel)')),'yyyy-MM-dd'),'yyyy-MM-dd') Where outputs('Compose_(hold_date_from_Excel is a compose element that holds the Excel date number. Best regards, Ludwig
craig99b1
Level: Powered On

Re: Convert Excel date value

Hi Ludwig,

 

I am not getting a correct output and not sure why...

Annotation 2019-02-16 224340.jpgdesign

 

Annotation 2019-02-16 224709.jpgoutput

Thanks for any help you can provide.

 

Craig

Johnny_Zhu
Level: Power Up

Re: Convert Excel date value

Anyone figure this out? I'm having same issues....Smiley Sad


ymaglaras
Level: Power Up

Re: Convert Excel date value

Hi!

 

After a lot of trial and error, I think I have a solution: ticks!

 

Here's how you construct a date taken from Excel Online:

add(mul(float(items('Array')?['Date']),864000000000),599264352000000000)

(I'm taking the date from a loop, but feel free to get it from elsewhere)

 

And here's how you turn today into ticks:

ticks(startOfDay(utcNow()))

(you can remove startOfDay if you care about the time, too)

 

What's going on with the first function: You take Excel's numeric date (days since 1/1/1900), e.g. 43267.234234826 as a float, then multiply it by 864000000000 to turn it into 100s of nanoseconds (i.e. ticks) and, finally, add the difference between 1/1/1900 and 1/1/0001 (in 100s of nanoseconds)

 

Highlighted
djcarver
Level: Powered On

Re: Convert Excel date value

Hi,

 

I am having similar issues but I'm also trying to convert the time (e.g. a serial like 43598.802997685 should read 13/05/2019 19:16). I've got the purely the date working, but can't get the time as well.

 

addDays(addDays(formatDateTime('1900-01-01T00:00:00'),int(items('Apply_to_each')?['Date'])),-2)

 

For context, I'm importing a load of stuff from an excel table into a SharePoint list.

 

Thank in advance!

ymaglaras
Level: Power Up

Re: Convert Excel date value

Hey,

 

AddDays can only add (or substract) whole days. By using int(), you strip your date of the time (but it's mandatory for addDays().

 

Perhaps you can use the decimal part of the date number and multiply it by 24 (and get the hours), then use the new decimal part and multiply it by 60 (and get the minutes) and, finally, take the last decimal part and multiply again (and round) by 60 (to get the seconds).

For example, 43598.802997685 -> 0.802997685 * 24 = 19,27194444 -> 0,27194444 * 60 =16,3166664 -> 0,3166664 * 60 = 19.

So the whole thing would become 13/05/2019 19:16:19

 

You can use int() to get the non-decimal part, then sub() it from the initial number, then mul() it by 24/60 and so on.

It's going to get a bit long. Good luck!

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 355 members 5,546 guests
Please welcome our newest community members: