New Twist on Time Conversion Problem

[pleasehelpSP]

Hi all,

I need to convert a time into a decimal, but also add a specific rounding function. For example, A1 shows the hours and minutes worked in the form of 8:39 (meaning 8 hours, 39 minutes). I want to convert this to a decimal in B1 and also round to the nearest quarter hour.

Where:
Minutes 0-7 = 0
Minutes 8-22 = .25
Minutes 23-37 = .50
Minutes 38-52 = .75
Minutes 53-0 = would round up to the next hour, with minutes at 0

So for the example of 8:39, B1 would need to read 8.75.

I also have a few instances when hours worked will be something like 14:04 - still meaning 14 hours and 4 minutes - I'm having trouble with Excel reading this as military time and only attributing 2 hours and 4 minutes. Anyway to overcome that?

- SP

 

[shg]

Perhaps =MROUND(A1, "0:15") * 24

 

[alansidman]

Try this =mround(a1,"0:15")

Alan

 

[pleasehelpSP]

Perhaps =MROUND(A1, "0:15") * 24

Ok awesome, that works great!

Here's an even dumber question for you - I am working on a previous employee's worksheet - and for some reason, sometimes Excel formats the time entered in the 12:00:00 PM format and no matter what I do I cant seem to overrule it! It displays correctly (12:00), but when you click on the cell that's when it shows you the 12:00:00 PM format. So it doesnt work with the formula you gave me. I've tried comparing the format to the ones that are correct, but I cant figure it out. I've tried to put it in other time formats but it doesnt seem to switch. If I try to change it to "General" it becomes a small decimal - I've been told thats because Excel views 24 as 1 for the day so 8hrs would be .333. Whats the deal??

 

[pleasehelpSP]

Nevermind, I'm just going to re-do the incorrect ones. I appreciate the help!

 

[shg]

It displays correctly (12:00), but when you click on the cell that's when it shows you the 12:00:00 PM format.

The value you see in formula bar comes from your control panel regional settings for time -- don't worry about it, as long as the time is formatted as you wish as it appears in the cell.