Excel Magic Trick 379: Round to Nearest 1/10th Hour for Payroll

I have a timesheet on a workorder. We charge by the hour, so if we are only there for 10 or 15 minutes we still have to bill an hour.

On the sheet:
M18 is time in
N18 is time out
O18 is the amount of time (=N18-M18)
P18 is what we charge for that time (=O18*24*P46)
P46 is the billing rate

If we are only there from say, 12:00 to 12:15, how do I get it to round that up to an hour? So the sheet shows how long we were there but bills the minimum hour.

I am working on a timesheet that keeps weekly tallies of in/out. I have everything I need EXCEPT I want my totals to round (up or down) to the nearest 1/4 hour.

For example, if someone worked from 8:00 am to 4:42 pm, their total hours are 8.70 - I want this to round to 8.75

Another example, if someone worked from 8:00 am to 4:32 pm, their total hours are 8.53 - I want this to round to 8.50

I'm sure there is something really easy I am missing here because this seems very simple as I type. Any help you can provide is greatly appreciated.

First of all, I am not very familiar with Excel lingo. Please go slow

I am trying to round to the nearest quarter hour on a payroll worksheet. I am using =MROUND (L2, 0.25). It will round to tenths, but not quarters. And stranglely enough, last week, my =MROUND formula rounded everything to the quarter. I added a password protected the sheet and the document has been viewing/modified in both 2007 and 2010. Could any of these factors coorupted the file? I copied and pasted the information to new worksheet, but I still have the same results.

I have been working on this for days!!! Any assistance you could provide would be greatly appreciated.

~ShellyB

I am not too understanding of excel to any great extent. But I need to round
time to the nearest quarter.

Example

8:11 to 8.25
4:41 to 4.75

Hi, I am trying to get my spreadsheet to round to the nearest quarter hour.
This is the current formula I am using.

=ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2)

As you can see it to decemal with breaks included. I still need to finnish
in decemal.

I tried to incorpuate a couple of formulas but fail.
=ROUND(A1*96,0)/96
=ROUND(D13*4,0)/4

Thanks

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Hello there.
First, and before all, I must admit that I am a kind a "dumb" for Excel, because I always were using it, only for better look's of simple tables, schedule's and similar. Although, I was all the time aware of great power's of this program, but newer tested it.
So, finally after few years of tempting, I decided to make a proper "order-list" for the shop in which I work. What I needed is to ,based on products and their prices, make a drop-down box, with list of the products, and after choosing one , proper price will be shown in next column. I find out how to do it with "Excel magic trick #5" on Youtube.
This is what i achieved:

http://i49.tinypic.com/dorzgn.jpg

However,
You see that in row 16, or line 2 in the order box ,we have #N/A below Cena (Price), Iznos PDV (amount of VAT), Cena sa PDV (Amount with VAT), and Iznos (Total amount). If we choose one of the product from drop-down box (now, as You can see none is chosen) and type number in Kolicina(Quantity) column, all this would be automatically changed into proper value.
But what if we have only one thing in order box, instead of 2 or all of 10 ? How to avoid those #N/A, and make instead to be blank cell, which is to be counted as 0, because of final calculation?

In this order-form we have only line 1 and 2 programed to have drop-down box in line Naziv robe (Name of the product). If I putted same drop-down boxes in rest 8 columns, #N/A would be shown in all programed cells bellow, like in line 2...

Kind regards from Serbia

Hello,

I am constructing a make-shift database in excel but can't seem to find a way to get magic trick #213 to work.

What I am trying to do is create a dynamic filter via 16 criteria. I would like any results that match all criteria that I specify and then displays results in a separate table to the right.

Thank you so much for your help - I would attach my spreadsheet to this message if I knew how.

BR,

PR

Is it possible to round up or down a 24HR time format.

ie. 18:06 would be 18:00
18:28 would be 18:30?