Forum Discussion
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 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.
See the attached for a solution using a custom VBA function. You'll have to allow macros when you open the workbook.
See the attached for a solution using a custom VBA function. You'll have to allow macros when you open the workbook.
- 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- EndTime = StartAndEnd(1)
'not next day add PM
if EndTime<StartAndEnd then
EndTime = StartAndEnd(1) & "PM"
end if
Total = Total + EndTime - StartTime
