Creating Time Sheet

[Guest]

I'm stuck. I'm trying to create a time sheet where if I enter time in decimal
format, it will display in hour format automatically. I also want it to
calculate the difference between the times (including lunch breaks). So, for
example, if I start at 9:45 am, I can input 9.75 (to be displayed as 9:45 am)
in A2, 17.00 in b2 (to be displayed as 5:00 pm), and a one-hour break in c2.
D2 would then calculate the total. I've experimented with different formulas,
but seem to run into a circular reference error. Any suggestions?

Thanks.

 

[David McRitchie]

Time is measured as a fraction of a day so 12 noon is .5 days
to convert hours in decimal to time divide by 24
to convert an Excel time to decimal multiply by 24

More information on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
Time Sheets
http://www.cpearson.com/excel/topic.htm
http://www.cpearson.com/excel/overtime.htm

You can't use a formula to refer to itself and that or referring to
another cell that refers to the cell with the formula (or more intermediates)
will result in a circular reference.

You probably want to enter your time as 9:45 with a colon
A2: 9:45
B2: 17:00
C2: 1

simple formula:
D2: =24* (B2-A2) - C2 format as decimal (hours)

to provide for time once through midnight
D2: =24*(B2-A2+(A1>B2)) - C2 format as decimal (to show hours)

(A1>B2) is a logical value is 1 if True or 0 if False so it adds 1 day if True

Please use your first and lastname in newsgroups, particularly technical newsgroups.