calculating time, overtime in excel [h]:mm format
 

I am trying to complete a spreadsheet in hour and minute format that will
calculate the overtime for each employee over 40 hours. If I have someone
with 45:30 hours and want to have excel calculate the hours of overtime in
the next column What formula would I use?
=MAX(0,D35-40) doesn't work
=IF($D3540,SUM($D35,-40)," ") doesn't work

calculating time, overtime in excel [h]:mm format
 

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"iamtaranicole" wrote in message
...
|I am trying to complete a spreadsheet in hour and minute format that will
| calculate the overtime for each employee over 40 hours. If I have someone
| with 45:30 hours and want to have excel calculate the hours of overtime in
| the next column What formula would I use?
| =MAX(0,D35-40) doesn't work
| =IF($D3540,SUM($D35,-40)," ") doesn't work

calculating time, overtime in excel [h]:mm format
 

=MAX(0,D35-"40:00")

format the cell as hh:mm


--


Regards,


Peo Sjoblom


"iamtaranicole" wrote in message
...
I am trying to complete a spreadsheet in hour and minute format that will
calculate the overtime for each employee over 40 hours. If I have someone
with 45:30 hours and want to have excel calculate the hours of overtime in
the next column What formula would I use?
=MAX(0,D35-40) doesn't work
=IF($D3540,SUM($D35,-40)," ") doesn't work

calculating time, overtime in excel [h]:mm format
 

Hi,

Try:

=MAX(0,(D35*24)-40)

HTH
Jean-Guy


"iamtaranicole" wrote:

I am trying to complete a spreadsheet in hour and minute format that will
calculate the overtime for each employee over 40 hours. If I have someone
with 45:30 hours and want to have excel calculate the hours of overtime in
the next column What formula would I use?
=MAX(0,D35-40) doesn't work
=IF($D3540,SUM($D35,-40)," ") doesn't work

calculating time, overtime in excel [h]:mm format
 

=IF(D36$D$1,D36-$D$1," ")
Thanks everyone This formula worked also where $d$1=40:00

"iamtaranicole" wrote:

I am trying to complete a spreadsheet in hour and minute format that will
calculate the overtime for each employee over 40 hours. If I have someone
with 45:30 hours and want to have excel calculate the hours of overtime in
the next column What formula would I use?
=MAX(0,D35-40) doesn't work
=IF($D3540,SUM($D35,-40)," ") doesn't work