Calculate elapsed time as a % of an hour

Question

I have an interesting challenge that may not be unique, so bringing it to this group for ideas.

We have a dataset that includes DATE and TIME as separate fields. On import, the DATE field is set to a date type m/dd/yyyy. Easy enough.
The TIME field is of the format: "00.00.00.000000". In a formula, I've substituted "." for ":".

Challenge #1: Using these available DATE and TIME fields, I want to calculate which hour of the day 0-23 the INTERVAL LENGTH applies to. I hope that makes sense. In other words, I want to somehow calculate that of the INTERVAL_LENGTH of 60.295, 2 minutes and 36 seconds occurred within hour #8 on April 2nd and the remaining 57 minutes and 42 seconds occurred within the 9th hour of April 2nd.

My thought is that if I could convert the DATE and TIME fields to a time stamp, I could then convert the TIMESTAMP and INTERVAL LENGTH fields to a common value like seconds and be able to do some time math...

Challenge #2: I can't seem to find a formula that will concatenate my DATE and TIME fields as a true time stamp. I've tested various approaches by calculating the interval using an Eval() formula and so far all of my attempts come up to 0.

Now, I may not even need to do any of this, I may have total and complete analysis paralysis at this juncture. I think this is probably achievable without a TIMESTAMP field if I use a reference table, but this is MF data so I want to do the absolute least amount of XREF I can before grouping my dataset by CEC, JOB, START DATE, END DATE, and HOUR.

Has anyone done some thing similar to this before? Tried to evaluate which side of an hour a duration of time falls within?

Thanks!!!

#TBMStudio

Accepted Answer

Hi Stephanie,
This is an interesting task. I worked it out in Apptio and created a video with a solution that I believe solves for what you're looking for. The video walks through the formulas and steps I used, and attached is an image of the end result. Let me know if this is what you were looking for.
https://youtu.be/23guyPlO_7k

Josh

#TBMStudio

Platform