@twilson The formula provided worked very well, thank you! I figured I could use a simple concat() function to add in the UTC time zone at the end of the ISO date.
I actually decided NOT to go this route in my project and I’m going to go a different direction to help my client. Mostly because keeping up with the time calculations and time zone readings became very complicated far too quickly. I’m just going to limit the amount of events pulled and sort them instead of limiting them to an end date.
However, here’s some notes:
- Calculating future date: When you use the NOW() function in A1, you can set A2 to be =A1+7, which will add 7 days to the original date. Then I took the future date and used your formula to format it in ISO 8601
- Scheduling an action: Per this article: http://support.intuilab.com/kb/how-to/how-to-use-excel-formulas , IF requires us to call an action to ‘set’ the value that Excel calculates. This means that I need to have one action when the XP starts (timer, after seconds, set text to be pulled from Excel) and also when the day changes, so that it continues to update (from clock IA, when day changes, set text to be pulled from Excel)
- Values from Excel not correct: When setting a text item or other asset as the ISO Date from Excel, it only reads the literal formula, not the date value. So the text asset in my XP will read yyyy-mm-ddThh:MM:ss
So anyways, maybe with more research I’ll find a better way to calculate a rolling 7 days ISO 8601 date range, that automatically updates with time zone changes. But for now, that’s where I’m going to leave it lol.
I always appreciate new ideas from the community…perhaps there’s a Time/Date API out there that will return a date range in a format with a simple GET request. That’d be cool.
