1

I have separate date and time fields for an event and the data entry of that event. How do I determine the hours between the event and the data entry?

The time field is a string in standard time which I converted to a military time time field using a formula.

date is written in mm/dd/yyyy format and time is in standard time hh:mm am/pm which I've converted to HH:MM military time.

If an event happened at 12:10 pm on 12/3/2016 but it was entered at 1:25 pm on 12/5/2016, I want to see 49 Hours and 15 Minutes in my final field

4444
  • 3,541
  • 10
  • 32
  • 43
isoman4not5
  • 131
  • 14

1 Answers1

0

DateDiff is just what you need. Something like:

DateDiff("h", DateTime({StartDate},{StartTime}), DateTime({EndDate},{EndTime}))
  & " Hours and " &
DateDiff("n", DateTime({StartDate},{StartTime}), DateTime({EndDate},{EndTime})) Mod 60
  & " Minutes"

This will retrieve the difference between your two dateTimes twice - Once in hours, once in minutes. Perform Mod 60 on the minutes section to get only the remainder minutes after subtracting 60 minutes for each hour.

4444
  • 3,541
  • 10
  • 32
  • 43
  • How do I combine the date and time fields prior to that? – isoman4not5 Oct 07 '16 at 17:03
  • `datetime({YourDateHere},{YourTimeHere})` (I've added it into my answer) – 4444 Oct 07 '16 at 18:04
  • 1
    That works. Thanks!. One last question: It has a " - " before each figure. How do I eliminate that? – isoman4not5 Oct 07 '16 at 18:29
  • Sorry, it's only working to a point. If I had an event time of 11:30 am on 12/3/2015 and then a data entry time of 11:25 am on 12/5/2015 I should see a result of 47 hours and 55 minutes instead I get 48 hours and 55 minutes – isoman4not5 Oct 07 '16 at 18:55
  • @isoman4not5 If you're still getting negative values try swapping the end and start values. Or adding an absolute value function. ...As for the extra hour, I'd try testing it out with some dummy dates to see if it's always 1 extra hour. If that's the case, just subtract 1 from the hour count. – 4444 Oct 07 '16 at 19:51
  • It's whenever the data entry time and the event time happened in the same hour but the event time came later than the data entry time relative to the respective day – isoman4not5 Oct 07 '16 at 20:43
  • @isoman4not5 - If I were you, I'd add Boolean logic to the formula to express what you just said. Here's a hint: You can determine which date came first by comparing them with `<` or `>` – 4444 Oct 07 '16 at 20:45
  • 1
    Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/125207/discussion-between-isoman4not5-and-4444). – isoman4not5 Oct 07 '16 at 20:49
  • That did it. Thanks. I was about to make it overly complicated and try to compare characters in the strings – isoman4not5 Oct 11 '16 at 15:00