Printable View
I was at work and my boss asked if I knew anything about excel. I said no. He was wondering if I knew how to have a formula that takes two times and figures out how many hours that person worked, I looked at the syntax and gave him the answer, but he wanted the time rounded to quarter of hours. How would I go about doing that. Here is what I have(I know really simple)Code:=c1-b1
well you can use the minute() function to extract the number of minutes in your calculation. Then you just to need to find a way to round it.
ok considering B1 is the from time and C1 is the to time... and d1 should display the number of hours then the following is the formulae in d1
but make sure b1,c1 and d1 cell format is that of timeCode:=HOUR(C1-B1)/24+(IF(MINUTE(C1-B1)<15,0,IF(MINUTE(C1-B1)<30,15,IF(MINUTE(C1-B1)<45,30,45)))/(24*60))
now the formulae will roud the time the nearest lower quater... this can be changed as required..
An easier way is to record the date in hh:mm format (IE 9AM as 09:00 and 5PM as 17:00). then you can add and delete and it will show the hours.
If you want to SUM the hours it gets a little weird...but if you change the cell format of the total cell to "[h]:mm" it works out AOK
Saves all those unsightly clunky formulas
See attached for a demo
[edit] Oh and this is better suited for the Tech board...Not GD