I have a report that gives me date/time in one cell (example: 11/29/2022 4:49:03 AM) which I split the date and time. What I need is to take the time and breaking it down into one of six four-hour time slots...12:00, 4:00 & 8:00 am & pm.
A little background info…inventory refills print out every four hours, six times a day, 12:00, 4:00 & 8:00 both am & pm. The actual refill time is what is recorded in the report.
I need a simple formula I can run in my power query that will convert the "Actual Refill" time into the "Refill Run" time as shown on the attached file.
Btw, I'm using 2016.
Hi Roy,
That would be by adding a custom column with the following code:
= Number.RoundDown([Actual Refill]/(1/6),0)*1/6)
as demonstrated in the attached file.
Riny
You're a GENIUS Riny!!! That's perfect. Thank you!
I kept getting an error with your formula until I realized I had to change the column type on the [Actual Refill] to decimal number. Then I was able to switch the custom column to time.
