Excel: Formula to convert "Chunks of time" into "Whole number" units for every 15 Minutes

[jacobconnell]

I need to convert every 15 minutes worked into a whole number Unit, rounding down if it hasn't reached the 15 minute increment.  Issue being that the times worked are not easy to measure as I may be working for 2 minutes, then later 8 minutes, and then later 30 minutes.  I currently have it expressed as shown in Column A on the attached Excel sheet, and I manually add up the time at the end of the day or whenever I need it and enter the information into Column B.  If anyone has an easy solution that wouldn't add too many extra cells, it would be appreciated.

Test Convert 

[#2 · Accepted solution]

@jacobconnell 

See the attached for a solution using a custom VBA function. You'll have to allow macros when you open the workbook.

[#3]

That actually works amazingly, thank you.

Apologies for not asking before, but is there a way to eliminate the need for the "PM" to be added for when a time chunk crosses 12:59PM?

Example: 11:49-1:23

Currently without adding AM to "11:49" and PM to "1:23", the function returns a negative number.

There will never be a case of multiple of these crossings in a single cell, and there will never be a case where a time chunk will go into the next day (As in "10:29AM-1:22AM" or "12:49PM-1:34AM" will never happen or be a concern).

I can definitely use it as though, just wanted to ask if that is possible/an easy fix. And again, I appreciate you sharing that fix

[#4]

@jacobconnell There's also a few formulas you could try, depending on if certain conditions are always true...

(1) If "AM" is always omitted and "PM" is always entered (ie: 10:50-11:07, 11:49-1:23PM, 1:38PM-1:45PM), use the following formula:

=ROUND(SUM(MMULT(--TRIM(TEXTSPLIT(SUBSTITUTE(A2, "PM", " PM"), "-", ",", TRUE)), {-1;1}))*96, 0)

(2) If "AM" is also sometimes entered (ie: 11:49AM-1:23PM), use an additional SUBSTITUTE function as follows:

=ROUND(SUM(MMULT(--TRIM(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2, "AM", " AM"), "PM", " PM"), "-", ",", TRUE)), {-1;1}))*96, 0)

Note: the SUBSTITUTE function is required in both of the formulas shown above because "1:00PM", for example, is not recognized as a valid time in Excel, whereas "1:00 PM" is.

(3) Regarding your additional inquiry about eliminating both "AM" and "PM" altogether, as long as there will never be a single time range greater than or equal to 12 hours (ie: 8:00-7:59 at the most), you could use the following formula:

=LET(arr, --TRIM(TEXTSPLIT(A2, "-", ",", TRUE)), ROUND(SUM(MOD(DROP(arr,,1)-TAKE(arr,,1), 0.5))*96, 0))

For example, 11:49-1:23 will return a value of 6, representing 6 intervals of 15 minutes.

[#5]

EndTime = StartAndEnd(1)
'not next day add PM
if EndTime<StartAndEnd then

EndTime = StartAndEnd(1) & "PM"
end if
Total = Total + EndTime - StartTime