Date/Time: Adding 10 min to a date/time value?

sara_llamasoft · November 23, 2012 10:57AM

Hi Folks - I'm probably missing something obvious, but here's what I need to do (working in US dates here):
- I have a date field [RouteDate], e.g. 8/12/2012 (Aug 12)
- I have a time field [StartTime], e.g. 12:45 (assume 24-hour time)
- I want to combine these into a date-time field and add 10 minutes, e.g. 8/12/2012 12:55. I'm trying to create a shipment delivery time window. Of course, I want this to be time-date math, so that if the time were 23:55, my new value would be 8/13/2012 00:05.

I tried many combinations of DATEADD, DATEVALUE, TEXTTODATE but nothing seems to yield the date-time value I'm after.

Any ideas would be most appreciated.

paola · November 23, 2012 12:10PM

Hi Sarah,
First you need to form a field that contains date and time by using a formula to merge the existing values:
TEXTTODATE(([Date]+" "+[Time]), "dd/MM/yyyy HH:mm:ss",true)
Result:
DATEADD([Date+Time], 10, "minute")

sara_llamasoft · November 24, 2012 10:28AM

Thanks, Paola. I tried permutations of this approach. It's possible that there's an Omniscope problem here with US settings if this worked for you in other settings. Because when I use your first TEXTTODATE statement, my result is Aug 12, 2012 with no time. However, if I check it with DATETOTEXT, then I can see the correct time component. I took it a step further and went on to the DATEADD portion where I received Aug 12, 2012 12:10 PM. So it did not properly see my time component.

To make it all work correctly, I used a DATETOTEXT on your first suggested function, then back again before the DATEADD function.

Thanks for the ideas!

Welcome!

Categories

Ideas Parade

Tagged