How to get date and time field from excel to outlook calendar event?

[tommoseley]

Trying to create a flow from Excel to create events in outlook calendar.

When I run it however it is creating all the events at the same time on the same day. I am assuming this is down to a missmatch in date and time formats between excel and power automate/outlook. 

The excel source is "30 Aug 2022, 00:00" in this format. How can I get this recognised so it creates an event at the correct time in outlook calendar?

Excel Source

Start Date/Time	End Date/Time
30 Aug 2022, 00:00	31 Aug 2022, 00:00
22 Aug 2022, 00:00	26 Aug 2022, 00:00

Thanks

[eliotcole]

Hi, @tommoseley, firstly, for anyone to be able to properly assist you here, we'll need to see some screenshots of your flow in that original question that you've made. Just edit it, and obfuscate where necessary. 👍

Secondly, and this isn't intended as sarcasm, but have you searched these forums for "excel date", or "convert excel date", or something similar? This has come up a *lot* and you'll easily find what you need here.

https://powerusers.microsoft.com/t5/forums/searchpage/tab/message?filter=location&q=convert%20excel%...

Please do see where I said 'something similar' and not just take it literally, do a little looking around, I'm sure you'll find what you need. 😉

More to the point, if you just do a StartPage on excel date format, it should help you understand how to interpret how excel stores dates. Then all you need to do is perform the requisite date formatting in Power Automate.

This search lead me to this page, which states:

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

So rather than working with whatever dates you're currently getting perhaps parsing that literal number is going to be easier. Obviously that's not the full thing, but it leads you where you'd need to go. For example, times are stored after a full stop, making it a floating point number, like 39448.625 will be 15:00 on that date.

Lastly, and this is one that you'll probably already know, and feel keenly, but if this information is being kept in an excel worksheet, then there's a very strong chance that it's being kept in the wrong place. Consider migrating the data to a SharePoint list (even easier to work with and make) where you can easily define date information, and perform much of the work that the excel sheet does with far greater ease.

---

Now, if the date is coming through into a Power Automate Excel action, and showing as that, then the date in the original sheet isn't provided with a time.

However, in my experience, unless the actual column in excel has been formatted to send the date in a text format, then you'll need to convert that number yourself. So, again, in my experience, if you absolutely have to work with excel, then create a 'helper column' in the original sheet which has the right format of text data from the crazy excel nonsense.

Once you have done that, then date formatting in PA is easily done with either the Convert Time Zone action (make both zones the same if you don't need to convert time zone) or by using a formateDateTime() function within an expression in a flow. Full documentation on that function is here, with format options here (custom).

Here's an example, where EXCEL_DATE should point to the date from the excel sheet, that will create a proper ISO date:

formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float(EXCEL_DATE),86400),'0'))), 'o')

[tommoseley]

Hi @eliotcole Thanks for your response. I did try to search the forum and try lots of different methods unfortunately losing two days of my life to it 😛

I have tried playing around with the format date and time but have the following error. Any ideas?

It appears to now be getting the date and time in the correct format below but doesn't like it still.

Start Time = formatDateTime(addDays('1899-12-30', int(items('Apply_to_each')?['Start Date/Time'])),'dd/MM/yyyy hh:mm')

End Time =  formatDateTime(addDays('1899-12-30', int(items('Apply_to_each')?['End Date/Time'])),'dd/MM/yyyy hh:mm')

Excel Source

[eliotcole]

I think you're formatting it wrongly, @tommoseley, when in doubt go ISO8601, or 'o' as it's known in the system. That will format it to the correct yyyy-MM-ddThh:mm:ss.0000000Z way which all applications will recognise (barring excel, of course, which is just silly) and use accurately. 

Also, if you really want to keep things super accurate, use the seconds, as per my example. Plus, like I said, your Excel source doesn't have times in there, so that's why they're all 00:00. 🙂

Also, again, get all of this stuff out of that Excel sheet and into a SharePoint/Microsoft list. 👍 It's the right place for the information, it's easier to manage, set-up, and append to, plus you can have it export to excel for anyone that still needs that legacy stuff. Excel is a calculator, not an information store. 😉

[tommoseley]

I tried that approach using the following expression in the Start date field on "Create event"

formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float('Start Date/Time'),86400),'0'))), 'o')

But get the error:

InvalidTemplate. Unable to process template language expressions in action 'Create_event_(V4)' inputs at line '0' and column '0': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Unfortunately the source data is auto created from another application but this can only be done to excel. I tried importing from excel to a sharepoint list but hit issues with the date again and random empty cells in the table causing it to crash.

[tommoseley]

I've had a look through the forum again and tried recreating the flow, opening it in incognito mode and changing the source but still comes up with the following error.

InvalidTemplate. Unable to process template language expressions in action 'Create_event_(V4)' inputs at line '0' and column '0': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

[tommoseley]

Nearly there. I've managed to get it to sync across to the calendar by using compose to generate the start and end times using formatDateTime(item()['Start Date/Time'],'o')

However, it created the events 1 hour later than specified in the time field. e.g. event from 8am to 6pm gets added as 9am to 7pm.

I have tried using the convert time zone but London shows up as UTC +0, which is what I believe it calculates in anyway so I'm not sure what to set the values as so that it still works when the clocks go forward and backwards as part of GMT?

[eliotcole]

Like I said before, if you're doing it using the 'Convert time zone' action, then you should use the same one for source and destination. However you'll also need to know what your timezone is set to in the Power Automate settings, and ensure that it is the same as that.

I recommend just using whatever you're putting in, no conversion. Also ... show what you have done to get whatever is in the 'Outputs' in that screenshot.

There's no reason why an extra hour should appear out of nowhere on your time if taken from the excel sheet properly.