Time Sheet Function to Calculate

[Mark McDonough]

I am having some trouble trying to establish a formula that will give me the
number of hours worked during the day given the start time say 8.30am with
an end time of 5.30pm and say 30 minutes for lunch. For this scenario, total
working hours would be eight and a half hours but I need Excel to display
this result as 8.30 hours (0.30 being the minutes in the half hour) and not
8.50. Can someone suggest what to do?

Typically the layout would be

Column A - Start Time say 8.30am
Column B - Time in minutes spent at lunch say 30 minutes
Column C - End Time say 5.30pm.
Column D - the calculating of the hours and minutes with a result of 8.30
representing 8hours and 30 minutes as against eight and a half hours (8.50).

Thanks.




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[C01d]

Use this formula for column D.

=C1-A1-B1/60/24

C1-A1 gives you the time difference between the start time and end time. We then subtract the lunch break time, expressed as a fraction of a day.

[Bob Phillips]

You asked this question two days ago, and I responded. What is wrong with
that suggestion

=end_time-start_time-time(0,30,0)

and format as time.

If you want it as 8.3

=INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
30,0))*24,1)*0.6

formatted as general

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
> I am having some trouble trying to establish a formula that will give me
the
> number of hours worked during the day given the start time say 8.30am with
> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
total
> working hours would be eight and a half hours but I need Excel to display
> this result as 8.30 hours (0.30 being the minutes in the half hour) and
not
> 8.50. Can someone suggest what to do?
>
> Typically the layout would be
>
> Column A - Start Time say 8.30am
> Column B - Time in minutes spent at lunch say 30 minutes
> Column C - End Time say 5.30pm.
> Column D - the calculating of the hours and minutes with a result of 8.30
> representing 8hours and 30 minutes as against eight and a half hours
(8.50).
>
> Thanks.
>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

[Mark McDonough]

I couldn't get it to work:

Isn't there an easier solution? There appears to be the use of range names
which I don't have and am not sure what range should be selected. Is the
range to be for all five days of the week only or to include the row of
labels at the top or just the cell on the one line.

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> You asked this question two days ago, and I responded. What is wrong with
> that suggestion
>
> =end_time-start_time-time(0,30,0)
>
> and format as time.
>
> If you want it as 8.3
>
> =INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
> 30,0))*24,1)*0.6
>
> formatted as general
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Mark McDonough" <[email protected]> wrote in message
> news:[email protected]...
>> I am having some trouble trying to establish a formula that will give me
> the
>> number of hours worked during the day given the start time say 8.30am
>> with
>> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
> total
>> working hours would be eight and a half hours but I need Excel to display
>> this result as 8.30 hours (0.30 being the minutes in the half hour) and
> not
>> 8.50. Can someone suggest what to do?
>>
>> Typically the layout would be
>>
>> Column A - Start Time say 8.30am
>> Column B - Time in minutes spent at lunch say 30 minutes
>> Column C - End Time say 5.30pm.
>> Column D - the calculating of the hours and minutes with a result of 8.30
>> representing 8hours and 30 minutes as against eight and a half hours
> (8.50).
>>
>> Thanks.
>>
>>
>>
>>
>> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==----
>> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
>> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----
>
>
>



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Bob Phillips]

There are no range names, I just used logical references as I don't know the
real ones. Substitute those with the cell references.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
> I couldn't get it to work:
>
> Isn't there an easier solution? There appears to be the use of range names
> which I don't have and am not sure what range should be selected. Is the
> range to be for all five days of the week only or to include the row of
> labels at the top or just the cell on the one line.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > You asked this question two days ago, and I responded. What is wrong
with
> > that suggestion
> >
> > =end_time-start_time-time(0,30,0)
> >
> > and format as time.
> >
> > If you want it as 8.3
> >
> >
=INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
> > 30,0))*24,1)*0.6
> >
> > formatted as general
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Mark McDonough" <[email protected]> wrote in message
> > news:[email protected]...
> >> I am having some trouble trying to establish a formula that will give
me
> > the
> >> number of hours worked during the day given the start time say 8.30am
> >> with
> >> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
> > total
> >> working hours would be eight and a half hours but I need Excel to
display
> >> this result as 8.30 hours (0.30 being the minutes in the half hour) and
> > not
> >> 8.50. Can someone suggest what to do?
> >>
> >> Typically the layout would be
> >>
> >> Column A - Start Time say 8.30am
> >> Column B - Time in minutes spent at lunch say 30 minutes
> >> Column C - End Time say 5.30pm.
> >> Column D - the calculating of the hours and minutes with a result of
8.30
> >> representing 8hours and 30 minutes as against eight and a half hours
> > (8.50).
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> > News==----
> >> http://www.newsfeeds.com The #1 Newsgroup Service in the World!
120,000+
> > Newsgroups
> >> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> > =----
> >
> >
> >
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

[Biff]

>Isn't there an easier solution?

Yes.

>> =end_time-start_time-time(0,30,0)

It's as easy as can be if you use conventional time notations like 8:30
instead of hacking it to be 8.30.

Biff

"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
>I couldn't get it to work:
>
> Isn't there an easier solution? There appears to be the use of range names
> which I don't have and am not sure what range should be selected. Is the
> range to be for all five days of the week only or to include the row of
> labels at the top or just the cell on the one line.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>> You asked this question two days ago, and I responded. What is wrong with
>> that suggestion
>>
>> =end_time-start_time-time(0,30,0)
>>
>> and format as time.
>>
>> If you want it as 8.3
>>
>> =INT((end_time-start_time-TIME(0,30,0))*24)+MOD((end_time-start_time-TIME(0,
>> 30,0))*24,1)*0.6
>>
>> formatted as general
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (replace somewhere in email address with gmail if mailing direct)
>>
>> "Mark McDonough" <[email protected]> wrote in message
>> news:[email protected]...
>>> I am having some trouble trying to establish a formula that will give me
>> the
>>> number of hours worked during the day given the start time say 8.30am
>>> with
>>> an end time of 5.30pm and say 30 minutes for lunch. For this scenario,
>> total
>>> working hours would be eight and a half hours but I need Excel to
>>> display
>>> this result as 8.30 hours (0.30 being the minutes in the half hour) and
>> not
>>> 8.50. Can someone suggest what to do?
>>>
>>> Typically the layout would be
>>>
>>> Column A - Start Time say 8.30am
>>> Column B - Time in minutes spent at lunch say 30 minutes
>>> Column C - End Time say 5.30pm.
>>> Column D - the calculating of the hours and minutes with a result of
>>> 8.30
>>> representing 8hours and 30 minutes as against eight and a half hours
>> (8.50).
>>>
>>> Thanks.
>>>
>>>
>>>
>>>
>>> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
>> News==----
>>> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
>> Newsgroups
>>> ----= East and West-Coast Server Farms - Total Privacy via Encryption
>> =----
>>
>>
>>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption
> =----