Excel Rounding up HELP

Hi All

I am new to this site and need major help.

I have a spreadsheet which works out the hours spent on certain projects. I need the Monthly total to work the hours worked into days rounded up to the nearest 1/4 of a day for eg...
Cell A31 = SUM(A4:A31) so I get the total hours worked per month.
Then
Cell A32 =A31/7 (as 7 is the hours worked per day and I will get the monthly total of days used per month)
If I put that I have worked for 2 & 1/2 as 2.50 in a cell it calculates it as 0.31 (I need this to round up to the nearest 1/4 hour!) Is there any way I can do this?

Thaks in advance

Jodie

Just to clear this up a little,.. can you type exactly what you'd like the result to be on this example of 7 / 2.5
with 1.75 being a qtr day "in theory"

You can get to tenths of an hour rather easily, but I can find no easy way to get to the closest quarter of an hour. It would require some rather creative and probably imperfect logic to do that.

You could get to tenths of an hour by doing something like this: Where 7 is equal to the number of hours in a day.

Try this.

Go into Visual Basic Editor in Excel (Alt + F11 or Tools...Macro -> Visual Basic Editory) and within your Workbook, Insert a new module (In left hand pane, right click on the VBA Project for your workbook and Select Insert -> Module). In your module, paste :-

Save your workbook (Ctrl + S)

Go back to your workbook. In cell A32 type:-
Not sure if this is the most efficient way of doing it, but I think it works. Haven't done huge amount of testing with it, but it should round up to the nearest 0.25.

Hope that helps.

Hi

I need the hours to add up first so my soreadsheet is first broken down into weeks (minus weekends) then I have a weekly total which calculates the weekly hours. At the end of each week the total should add up to 28. Then after the last working day of the month I have a monthly figure which is all the hours in a day added to give me the hours per month. I then have a sum which is hours per month divided by hours per day (in theory this should give me a monthly daily total spent on a projecy) I need it to work out if I am only spending 2.5 hours on a project this should round up to the nearest 1/4 day. same if I only spent 1.5 hours on a project it would round down to the full day? Does this make sense?

Thanks
Jodie

Hi thanks for this but I keep getting a compile error sub or function not defined. Do you know why this is?

Thanks
Jodie

I guess it's saying it can't find the function get_quarters.

Did you paste the full function into a Module of the VBAProject for your spreadsheet?

As in the instructions above.

You must also save the spreadsheet before using the function.

Could you post a screenshot of the Visual Basic Editor showing your function?

Function get_quarters(ByVal value)
Dim main_value
main_value = value / 7

Dim arr_value
arr_value = Split(main_value, ".")
Dim decimal_value
decimal_value = "0." & arr_value(UBound(arr_value))
whole_value = arr_value(LBound(arr_value))

Select Case decimal_value
Case Is > 0.75
get_quarters = whole_value + 1
Case Is > 0.5
get_quarters = whole_value & ".75"
Case Is > 0.25
get_quarters = whole_value & ".5"
Case Is > 0
get_quarters = whole_value & ".25"
Case Else
get_quarters = whole_value
End Select

End Function

This is what I have entered. (Sorry couldn't get a screen shot!) The top line is highlighted yellow and the word 'split' is highlighted gray on the 5th line down. Thanks for all your help on this as I am seriously considering launching PC out of nearest window LOL =)

jodie

I've attached a test spreadsheet that contains the function.

Unzip the file and then see if the spreadsheet works on your machine.

You'll have to Enable Macros when the warning appears.