How do I subtract one time from another to get OT worked?

[Guest]

I am trying to design a simple excel spreadsheet that will calculate overtime
worked. I want it to display similar to this. The spreadsheet would
hopefully be a template and password protected, so all one would have to do
is fill in the blanks.

Sunday 13:30 17:30 4
Monday 17:00 18:00 1
Tuesday 17:00 17:30 .5
etc
TOTAL 5.5

Any suggestions, as I am stymied on how to do this. I am using Excel 2003.
Thanks.

 

[Guest]

See:

http://www.cpearson.com/excel/overtime.htm


Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
try
=(B1-A1)*24

 

[Guest]

Format all the cells involving times the same way (including the cell that
holds the calculation). For example, select the cells containing the data,
go to Format>Cells>Number then select 'Time' from the category section then
choose 13:30 from the 'Type' section.

Then go back to your spreadsheet and use a simple subtraction of the 2 cells.

A1 B1 C1
13:30 17:30 =B1-C1

You should see 4:00 as your answer.

 

[Guest]

Thanks so much. I appreciate the link. It will be every helpful. BTW, This
is the first time I've ever used a "Discussion Group." I think I'll use more
often when I have problems like this. Again thanks for the help.

 

[Gord Dibben]

Michelle

Chip Pearson has done all the work for you.

See http://www.cpearson.com/excel/overtime.htm

Gord Dibben Excel MVP