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 ...
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
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
'not next day add PM
if EndTime<StartAndEnd then
EndTime = StartAndEnd(1) & "PM"
end if
Total = Total + EndTime - StartTime