Need Help to Create a Date Range

Help from Community
1

As I’m pulling events from a Google Calendar with API Explorer, the parameters allow me to specify a date range for which to pull the events.

I want to always show events from Today plus the next 7 Days. Google calendar will work with the ISO 8601 Date that is found in the Clock interface asset. (for example today is 2017-07-31T20:03:42-05:00)

I just need a way to convert this date to always be 7 days ahead…so in this case I need the date to be 2017-08-07T12:01:00-5:00…and always stay up to date.

Having a little trouble with this - thanks for any ideas!

2

try using excel to convert the date
bring in the file as an interface assett
then map cell B2 to your query. worth a try!
Cell A1(name):
date norm
cell A2(name):
ISO date
cell B1:
=NOW()
cell B2:
=TEXT(B1,“yyyy-mm-ddThh:MM:ss”)

this won’t include the time zone conversion, but perhaps google that line of query if you need that.

1 Like
3

Thanks Tom, my next step was to have Excel do the work, so I really appreciate you posting a possible solution. I’ll report back with the method I’ll be using!

4

@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.

1 Like
5

Fantastic, glad to know it helped,
I had similar issues doing an activation, where I had a “Whats On” page and needed similar things to happen.
Best of luck!
Tom

1 Like