Help with Time Sheet

[CRobey4393]

Hi, My name is Chuck and I am trying to write a spreadsheet to be used as a daily timesheet.

The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in from lunch and f6 is Time Out.

The formula I am using for total time is (d6-c6)+(f6-e6)

I need to round this to the 1/4 hour.

Any help is appreciated.
Chuck

[bpeltzer]

=ROUND(((D6-C6)+(F6-E6))*96,0)/4
Times in Excel are just fractions of a day, so you'd normally multiply by 24
to convert a time (or a time difference) to a number of hours. Multiply by
another 4, round, and divide by 4 to get the nearest quarter-hour.
HTH. --Bruce

"CRobey4393" wrote:

>
> Hi, My name is Chuck and I am trying to write a spreadsheet to be used
> as a daily timesheet.
>
> The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in
> from lunch and f6 is Time Out.
>
> The formula I am using for total time is (d6-c6)+(f6-e6)
>
> I need to round this to the 1/4 hour.
>
> Any help is appreciated.
> Chuck
>
>
> --
> CRobey4393
> ------------------------------------------------------------------------
> CRobey4393's Profile: http://www.excelforum.com/member.php...o&userid=28257
> View this thread: http://www.excelforum.com/showthread...hreadid=478463
>
>

[JRod]

Suppose that the result is in D2. Try the formula:
=INT(D2*1440/15+0.4999)/1440*15

--
JRod
Microsoft MVP - Excel
http://EXCELer.blogspot.com

"CRobey4393" <[email protected]>
escreveu na mensagem
news:[email protected]...
>
> Hi, My name is Chuck and I am trying to write a spreadsheet to be used
> as a daily timesheet.
>
> The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in
> from lunch and f6 is Time Out.
>
> The formula I am using for total time is (d6-c6)+(f6-e6)
>
> I need to round this to the 1/4 hour.
>
> Any help is appreciated.
> Chuck
>
>
> --
> CRobey4393
> ------------------------------------------------------------------------
> CRobey4393's Profile:
> http://www.excelforum.com/member.php...o&userid=28257
> View this thread: http://www.excelforum.com/showthread...hreadid=478463
>

[JRod]

Another approach, brought By Norman Harker sometimes ago:
=ROUND((D2)/"00:15:00";0)*"00:15:00"

P.S.- don't forget, in both cases, to format the cell as Time hh:mm

Regards

--
JRod
Microsoft MVP - Excel
http://EXCELer.blogspot.com

"JRod" <[email protected]> escreveu na mensagem
news:[email protected]...
> Suppose that the result is in D2. Try the formula:
> =INT(D2*1440/15+0.4999)/1440*15
>
> --
> JRod
> Microsoft MVP - Excel
> http://EXCELer.blogspot.com
>
> "CRobey4393" <[email protected]>
> escreveu na mensagem
> news:[email protected]...
>>
>> Hi, My name is Chuck and I am trying to write a spreadsheet to be used
>> as a daily timesheet.
>>
>> The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in
>> from lunch and f6 is Time Out.
>>
>> The formula I am using for total time is (d6-c6)+(f6-e6)
>>
>> I need to round this to the 1/4 hour.
>>
>> Any help is appreciated.
>> Chuck
>>
>>
>> --
>> CRobey4393
>> ------------------------------------------------------------------------
>> CRobey4393's Profile:
>> http://www.excelforum.com/member.php...o&userid=28257
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=478463
>>
>
>

[Sandy Mann]

"JRod" <[email protected]> wrote in message
news:[email protected]...
> =INT(D2*1440/15+0.4999)/1440*15

Seems like an awful lot of calculating, why not just:

=ROUND(D2*96,0)/96

--
Regards


Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk


"JRod" <[email protected]> wrote in message
news:[email protected]...
> Suppose that the result is in D2. Try the formula:
> =INT(D2*1440/15+0.4999)/1440*15
>
> --
> JRod
> Microsoft MVP - Excel
> http://EXCELer.blogspot.com
>
> "CRobey4393" <[email protected]>
> escreveu na mensagem
> news:[email protected]...
>>
>> Hi, My name is Chuck and I am trying to write a spreadsheet to be used
>> as a daily timesheet.
>>
>> The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in
>> from lunch and f6 is Time Out.
>>
>> The formula I am using for total time is (d6-c6)+(f6-e6)
>>
>> I need to round this to the 1/4 hour.
>>
>> Any help is appreciated.
>> Chuck
>>
>>
>> --
>> CRobey4393
>> ------------------------------------------------------------------------
>> CRobey4393's Profile:
>> http://www.excelforum.com/member.php...o&userid=28257
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=478463
>>
>
>

[JRod]

Sandy,
This is really another approach, ok!! But all of the solutions they seem
that have the same problem: the rounding is made by deffect (I don't know if
this is the right word...perhaps "down"?) I found another approach presented
by Jon Peltier, that seems work better, because if it is, for instance
17:02, goes to 17:15 and not to 17:00... The formula is: =CEILING(D2,D3)
D2 is the result and D3 has the content: 0:15... In that case, the formula
gets a rounded up value.

Regards.
--
JRod
Microsoft MVP - Excel
http://EXCELer.blogspot.com

"Sandy Mann" <[email protected]> escreveu na mensagem
news:[email protected]...
> "JRod" <[email protected]> wrote in message
> news:[email protected]...
>> =INT(D2*1440/15+0.4999)/1440*15
>
> Seems like an awful lot of calculating, why not just:
>
> =ROUND(D2*96,0)/96
>
> --
> Regards
>
>
> Sandy
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "JRod" <[email protected]> wrote in message
> news:[email protected]...
>> Suppose that the result is in D2. Try the formula:
>> =INT(D2*1440/15+0.4999)/1440*15
>>
>> --
>> JRod
>> Microsoft MVP - Excel
>> http://EXCELer.blogspot.com
>>
>> "CRobey4393" <[email protected]>
>> escreveu na mensagem
>> news:[email protected]...
>>>
>>> Hi, My name is Chuck and I am trying to write a spreadsheet to be used
>>> as a daily timesheet.
>>>
>>> The way it is set is: c6 is Time In, d6 Time out for Lunch, e6 Time in
>>> from lunch and f6 is Time Out.
>>>
>>> The formula I am using for total time is (d6-c6)+(f6-e6)
>>>
>>> I need to round this to the 1/4 hour.
>>>
>>> Any help is appreciated.
>>> Chuck
>>>
>>>
>>> --
>>> CRobey4393
>>> ------------------------------------------------------------------------
>>> CRobey4393's Profile:
>>> http://www.excelforum.com/member.php...o&userid=28257
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=478463
>>>
>>
>>
>
>