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.

 

[C01d]

Use this formula for column D.

=C1-A1-B1/60/24

C1-A1 gives you the time difference between the start time and en
time. We then subtract the lunch break time, expressed as a fraction o
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)

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]

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)

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.





----== 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.

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