I'm creating a time sheet in Excel 2000. I use the [h]:mm format. I have to be able to enter time in increments of 1/4 hour, such as 0:15, 2:30, 1:45 and so on. I then use IF formula of, =IF(F30>40,40,F30). This formula works great with time entered in decimal format such as 1.25 or 6.75 but if you change the formula to 40:00 and enter the times in [h]:mm format, it goes crazy and gives wild answers. I'm under the gun to get this timesheet done and I've run out of hair to pull out! Please help...

Hi, First some background information on time in Excel.

Excel has a system for holding dates and times. The date is held in the integer or whole number part of a number and the decimal part holds the time.

For example 1.25 is 1 day plus a quarter of a day, i.e. 30 hours (24 + 6)

6.75 is 6 and three-quarter days, i.e., (6*24) + 18 = 162If you format cells as [h]:mm Excel displays the results as the total number of hours and minutes, and will show 30:00 and 162:00.

The next consideration is that when you enter data into a cell, Excel tests it to see if it is in a date or time format.

If you enter "40:00" Excel treats this as a time format and stores it as 1.66667, i.e. (1*24) + (24*0.66667) = 40.

Here is a solution:

The solution is based on creating the correct Excel date/time number for the hours and minutes entered, including rounding to the nearest 15 minutes.In Cell A1 enter "Hours"

In Cell B1 enter "Minutes"

In Cell C1 enter "Result"

Select cell A2 and from the Menu click Data then Validation...

Select the 'Settings' tab and under 'Allow:' choose 'Whole number'

In 'Data:' select 'between' and enter 0 (zero) and a maximum number of hours that may be entered - use whatever suits your situation.

Click OK.

Repeat the data validation for Cell B2, with a minimum of 0 and a maximum of 59This means that the time entry will be hours into Cell A2 and minutes into Cell B2. Users will have to enter whole numbers in both cells with a maximum of 59 in the minutes cell.

Choose a cell for the result:

I used cell C2

In Cell C2 enter this formula:=INT(A2/24)+(A2-24*(INT(A2/24)))/24+(ROUND(B2/15,0)*15)/(24*60)

Select Cell C2 and enter the custom format [h]:mmCell C2 will now show hours and minutes to the nearest quarter hour.

I don't know what you are going to do with the result (in Cell C2 in my example), so I can't advise on how to handle the result, but to help you see what is happening, do this: In Cell D2 enter the formula =C2

Now format Cell D2 as a number with 5 decimal places.Here are two 'pairs' of results showing rounding:

A B C D 1 Hours Minutes Result Value 2 23 52 23:45 0.98958 A B C D 1 Hours Minutes Result Value 2 23 53 24:00 1.00000 A B C D 1 Hours Minutes Result Value 2 93 6 93:00 3.87500 A B C D 1 Hours Minutes Result Value 2 93 8 93:15 3.88542Here is how the formula works:

A. INT(A2/24) gets the number of whole days from the hours entered.

B. +(A2-24*(INT(A2/24)))/24 takes the hours in cell A2 and subtracts the number of hours in the complete days - that's the 24*(INT(A2/24). This remainder is now divided by 24 to obtain the fraction of a day for that number of hours.

C. +(ROUND(B2/15,0)*15)/(24*60) takes the minutes entered, divides by 15 to get quarters of an hour and then rounds it to the nearest quarter. It multiplies the rounded quarter by 15 to return it to minutes and finally divides this by the number of minutes in a day (24*60) to get the fraction of a day represented by 0, 15, 30 or 45 minutes.I wasn't clear what you were trying to achieve with your IF() function.

If you still need help on it, please reply with details of what you want the formula to do and what outcome you expect - an example would be helpful.Regards

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History