Check out some similar questions!

JulesF · Feb 14, 2012, 12:40 PM

Hi, hoping someone can help me. I think I am trying to find one formula to do two things here. The first is to change the way my hours.minutes are displayed. For example, if I enter 1.30 (1hour30mins) into a cell, I would like it automatically displayed as 1.50 (1 and a half hours) (so 1.15 would be 1.25 and 1.45 would be 1.75 and so on. The second thing I am trying to do, in the same formula, is also to automatically round up/down to the nearest quarter of an hour. Example, 1.29 (1 min 29 secs) to round up to 1.50 (one and half hours). Hope this is understandable? Thanks.

jcaron2 · Feb 14, 2012, 12:57 PM

To convert from hours.minutes to decimal hours, you need to take everything after the decimal point and multiply by 100/60. You could do it this way:

=ROUNDDOWN(A1,0)+(A1-ROUNDDOWN(A1,0))*100/60

where A1 is the cell you're converting.

If you then want to round to the nearest quarter hour, you multiply by four, round, then divide by four again.

=(ROUND(B1*4,0))/4

In this case, B1 is the cell you're operating on. Replace B1 with the entire first formula in parentheses, and you'll have the entire thing in one shot:

=(ROUND((ROUNDDOWN(A1,0)+(A1-ROUNDDOWN(A1,0))*100/60)*4,0))/4

joypulv · Feb 14, 2012, 01:19 PM

I don't see a way, unless you use multiple cells and complicated formulas.
Excel can do math on time including am to pm, so why not subtract exact clock times, then round with one formula in 4 columns (some hidden)? You need an IF statement for each of 4 ranges of minutes.

joypulv · Feb 14, 2012, 01:21 PM

jcaron2 has a good suggestion

JBeaucaire · Feb 14, 2012, 01:27 PM

You're reinventing wheels here. You do not enter 1hr30mins in a cell as 1.30, you enter it as 1:30.

Then when you do math on that cell, you multiply by 24.

A1: 1:30
B1: =A1*24 (formatted as General afterwards)... will display 1.5

Now, to add the rounding, using the MROUND() function which lets you set the value to round to.

B1: =MROUND(A1*24, 0.25)

JulesF · Feb 15, 2012, 02:50 AM

Thanks for taking the time to reply.

jcaron2 - I have applied the formula you kindly suggested but I am getting a #VALUE error in the cell now.

JBeaucaire - when I enter my time duration as 1:01 it changes that cell to 0.5 so I have changed the format from number to time, but now it displays it as 01:01:00 which is too long! I would never be entering seconds into the cell.

I really want to crack this one... any help much appreciated.

joypulv · Feb 15, 2012, 03:47 AM

Under format you can select number of decimal places.

jcaron2 · Feb 15, 2012, 07:37 AM

If you have the option of typing in the time using a colon (e.g. 2:36, rather than 2.36), as JBeaucaire suggested, that's certainly the best option.

That being said, I don't know why the formula should be giving you problems! I just verified that if I type 2.12 in cell A1, then copy and paste the formula exactly as written above into cell B1, the contents are displayed as 2.25.

I attached my Excel file below. You should be able to download it and see what I did. I also implemented JBeaucaire's simpler method (in cells A5 and B5). You should be able to download the file and verify that it works for you as well. Note that for cell A5, where I typed in the time as 2:12, I changed the cell format to custom, with type "[h]:mm". That keeps the seconds from displaying.

JulesF · Feb 15, 2012, 01:58 PM

Thanks for your help. Today has certainly been an education in Excel. I have formatted the cells as you suggested, bingo! And a friend of mine helped me with the formula:

=HOUR(ROUND(A2*96,0)/96)+MINUTE(ROUND(A2*96,0)/96)/60

and this is working perfectly. Thanks again..

Mickg001 · Sep 6, 2012, 07:59 AM

Assume Col A holds Start Time, Col B holds End Time Rounded difference should be in Col C:
=IF(AND($C22="",$B22=""),"",INT(($C22-$B22)*96)/4)

Result is If either Col A or Col B are blank, Col C will be blank.
If Cols A & B Contain a time Col C will contain the difference rounded to the nearest 1/4 hour expressed as a fraction if Formatted as a 2 digit number e.g. 1.75 [Hours].

Darkthorne · Oct 22, 2012, 01:09 PM

Originally Posted by Mickg001

Assume Col A holds Start Time, Col B holds End Time Rounded difference should be in Col C:
=IF(AND($C22="",$B22=""),"",INT(($C22-$B22)*96)/4)

Result is If either Col A or Col B are blank, Col C will be blank.
If Cols A & B Contain a time Col C will contain the difference rounded to the nearest 1/4 hour expressed as a fraction if Formatted as a 2 digit number e.g. 1.75 [Hours].

"IF(AND" should be "IF(OR". With AND both need to be blank to return blank. Thanks