Forum Discussion

jacobconnell's avatar
jacobconnell
Copper Contributor
Nov 09, 2023
Solved

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

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 ...
excel
Formulas and Functions
  • jacobconnell's avatar
    jacobconnell
    Copper Contributor
    Nov 10, 2023
    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
    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Nov 10, 2023
      EndTime = StartAndEnd(1)
      'not next day add PM
      if EndTime<StartAndEnd then

      EndTime = StartAndEnd(1) & "PM"
      end if
      Total = Total + EndTime - StartTime
    • djclements's avatar
      djclements
      Silver Contributor
      Nov 10, 2023

      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.

Resources