Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Rounding Timesheet to quarter hour

I have made a time sheet and am trying to have the total hours and grand total- round up to the nearest quarter hour, I.E. (.25, .50, .75. 00), if anyone can help me please it will greatly be appreciated, this is what i have now, in my totals fields:

ROUND(IF((OR(B13="",C13="")),0,IF((C13<B13),((C13-B13)*24)+24,(C13-B13)*G1424))+IF((OR(E13="",F13="")),0,IF((F13<E13),((F13-E13)*24)+24,(F13-E13)*24)),2)

I Have also attached the file so you can see it completely, if you can help me fix it i would really really appreciate it.


Post your answer or comment

comments powered by Disqus
I've got a daily time-tracking spreadsheet. The formula in A4 is:

=(A2

I've got a daily time-tracking spreadsheet. The formula in A4 is:

=(A2<A1)+A2-A1+(A3<B2)+A3-B2

(A1 = Start Time, A2 = Lunch Begin, B2 = Lunch End, A3 = End Time, A4 =
Total Hours Worked)

Formatted as "[h]:mm"

The formula works great, but I need the total hours to be rounded to the
nearest quarter hour (.00, .25, .50, .75) Right now, if the total is 7 and a
half hours, it comes out as "7.30"

I've sent this question in previously and I believe the answer was to
multiply the total by a number (can't remember what it was), but I needed to
know how to fit that into the formula above properly and don't remember
getting a response from that question.

Hope that isn't too confusing.

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 have a column with date and time(column A) and i want to roundup the
time to every quarter and then separate the date(column B) and time
(column C)

A B C
3/14/2006 10:43PM 3/14/2006 10:45PM
3/14/2006 11:34PM 3/14/2006 11:45PM
3/14/2006 11:52PM 3/15/2006 12:00AM

note that since 11:52PM rounds up to 12:00, i want to change the date
to the next day.

--
kdp145
------------------------------------------------------------------------
kdp145's Profile: http://www.excelforum.com/member.php...o&userid=29594
View this thread: http://www.excelforum.com/showthread...hreadid=522318

i have a column with date and time(column A) and i want to roundup the time to every quarter and then separate the date(column B) and time (column C)

A B C
3/14/2006 10:43PM 3/14/2006 10:45PM
3/14/2006 11:34PM 3/14/2006 11:45PM
3/14/2006 11:52PM 3/15/2006 12:00AM

note that since 11:52PM rounds up to 12:00, i want to change the date to the next day.

I have made a time sheet and am trying to have the total hours IN COLUMN M TO INCLULDE HOLIDAY SICK AND VACATION HOURS AND BE CALCULATED WITHOUT THERE BEING ANY HOURS SPECIFIED IN THE TIME IN AND TIME OUT COLUMNS. I NEED THE TOTAL IN COLUMN M TO ROUND UP OR DOWN TO THE NEAREST QUARTER HOUR. THEN I NEED MY TEXT BOX NEXT TO TOTAL HOURS TO REFLECT THE GRAND TOTAL ALSO ROUNDED UP OR DOWN TO THE NEAREST QUARTER.

IF ANY ONE CAN HELP ME IT IS GREATLY APPRECIATED.
THIS IS WHAT I HVE NOW IN MY TOTALS FIELDS:

=ROUND((G13-C13-F13+D13+J13+K13+L13)*96,0)/4

I Have also attached the file so you can see it completely, if you can help me fix it i would really really appreciate it

I'm having a problem trying to get the decimal of a number to convert(may not be the correct term) to quarters. What I have is a time sheet that my office is wanting to automate in Excel. It must contain 24hr time entries which I have the cells formated to 00:00 because they didn't want to have to enter the colon. They want to be able to just enter 1030 and have it display as 10:30. At the end of the day there is a cell for the Total Hours Worked and is formatted as 0:00. There is another section that reports time taken off in hours like 4 or 2.5 and this is formatted as a number with two decimal points.

Since the time format is 00:00, when 10:00 is entered excel reads it as a number 1000. This is no problem except when it totals the time for that day. The Total Hours Worked for the day is calculated as Ending time minus Starting time. Example, if an employee works from 8:00 until 10:30, the formula is [1030 - 800 = 230] and is displayed as 2.3 [format: 0:00]. This is correct, the employee did indeed work two hours and thirty minuets however, I need for it to be displayed as 2.5 hours. Because minutes, for time keeping purposes, are rounded to quarter hours I need is some way to convert(again, may not be the correct term) the decimals in the following order:

.1 - .25 = .15
.26 - .50 = .5
.51 - .75 = .45
.76 - .99 = 1.0

I am not very well versed in VB so if there is a non-VB solution to this it would be preferred.

My formula calculates the time 2 hours from now and I need it to round up to
the next hour:

=NOW()+TIME(2,0,0)

I tried a few things but they didn't work!

How can i limit data input into a cell to quarter hour format. that is, entry will be limited to: 0.25, 0.50, 0.75, 1.00, 1.25, 1.50...etc

I thought a list might do it but then it gets difficult when the amounts range from half an hour to two or three days.

I'm working on a timesheet for my office. I need to calculate hours worked each day and round them down to quarters - 0.0, 0.25, 0.5, 0.75. I've tried ROUNDDOWN((A2-A1)*24*4,0)/4 but it does not work in all situations. For example if A2=12:15 and A1=10:00 the formula returns 2.00. The correct result should be 2.25. Does anyone have a solution? Thanks!

I am fixing our timesheet so the weekly hours total up in the regular hours column and round to the nearest quarter hour. In addition I would like any hours in excess of of 40 hours to drop in the OT column which I have done. My problem is getting the time to round properly. In the first week 37.30 should be 37.25. Does that seem correct to you? Have attached for review. Thanks.

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

If we have a column of times we're adding up in a formula like, say,
=sum(C4:C20) and we're dealing with times is there a way to modify
that formula so that it rounds the total to the nearest quarter hour?

p.s., since the spreadsheet had to take into account "negative" hours
in terms of subtracting hours, the spreadsheet is set to the 1904
system. Don't know if that makes any difference to the above.

Tx.

Hi

If I've got a cell which is adding together times in other cells, can I do anything to set it to round the answer to nearest quarter hour up or down.

Thanks

Julia

For a timesheet, I need a formula for the difference in time, start & end, rounded to nearest quarter hour, but if start time is :07 and after it goes to :15. Is that possible? The time formula I have is

=(ROUND(((b1-a1+(b1

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

I need help with a function that will round time (hh:mm) to the nearest
quarter hour. Example:

10:08 AM = 10:15 AM
8:37 PM = 8:30 PM

The control is 7 minutes after the quarter round down and 8 minutes after
the quarter round up.

Any and all help is GREATLY appreciated!!!! Thanks in advance.

J

I have a sheet that calculates elapsed time between two cells. This works fine.

I need to convert the time value to fractional hours and round it to the nearest quarter hour. (half-adjust)

Start End Value to be converted
Examples: 1:45 2:15 = 0:30 convert this to .5 hours;
1:45 2:21 = 0:36 convert this to .5 hours;
1:45 2:23 = 0:38 convert this to .75 hours.

The cell that is to be converted is formated as time hh:mm.

The result of the conversion will be placed in the cell immediately to the right of the original time cell. (not part of the problem)

Any help would be greatly appreciated.

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

Can I have a formula that takes an amount, adds 1.25, then rounds up to the nearest quarter or half dollar?

ItemCostRetailSelect Jumbo Pecan Halves$5.95Recipe Ready Pecan Pieces$5.85Pecans, Roasted and Salted$7.40Almonds, Unblanched, Whole, Roasted, Salted$5.00Almonds, Smokehouse$5.50

In this example, I need to be able to take a column of cost figures and add 1.25, then round to the next .25. So, for Select Jumbo Pecan Halves, the formula would return $7.25.

I am trying to find the best formula to demonstrate the length of time
it takes to perform tasks and then round them to quarter day
increments. For example, a task takes 10 hours to perform I need a
formula that will take the 10 hours and display 1.25 (assuming an 8
hour work day).

By the same token though if it is 10.5 hours it should roundup to 1.5
and not display 1.3.

Any help would be greatly appreciated.

--
toastnbutter
------------------------------------------------------------------------
toastnbutter's Profile: http://www.excelforum.com/member.php...o&userid=25511
View this thread: http://www.excelforum.com/showthread...hreadid=389556

I have a formula that results gives results like 22.63. Is there a way
to get it to round up to the nearest quarter number. For example 22.63
would round up to 22.75 or 133.10 would round up to 133.25.

Is this possible?

--
djarcadian
------------------------------------------------------------------------
djarcadian's Profile: http://www.excelforum.com/member.php...o&userid=15877
View this thread: http://www.excelforum.com/showthread...hreadid=479639

How do you round numbers to the nearest quarter?

Ok.. Is trying to sort out how to make the following.
I need a timesheet to record hours and minutes that a person has worked in a day.
I can make a basic one which calculates hours. but want to have one that successfully calculates minutes.

for instance if a person starts at 8:12 am and finishes at 12:14pm
then restarts at 1:13pm and finishes at 5:13pm.

the work i am at uses time cards witha military time format.
however people will also write in normal time as well .

Is it possible to figure out a combination of cell format and formula to make this work..

have a sheet that I adapted from something i found on the web but if i change calculations it doesnt seem to work right.

it gives calculations to 100 minutes.

Tried to use a basic [h]:mm format but that didnt work.

Im not sure if i have the cell formats correct or the formulas correct.
adapted a formula and cell format I found.
but would much rather try and learn how to do it so i understand.

on the time input cells it is formatted as
CUSTOM
[$-409]h:mm AM/PM;@
Basically I want to be able to enter EITHER military time (on the stamped timesheets) or standard time indicating either AM or PM so I guess it is H:mm AM/PM format..

the calculated fields is formatted as
CUSTOM
_(* #,##0.00_);_(* (#,##0.0);;_(@_)

=ROUND(IF((OR(C12="",D12="")),0,IF((D12


No luck finding an answer? You could always try Google.