UNP

How to Calculate Total hours and Overtime in Excel

Pleaseee I need formula for calculating total hours and overtime. For overtime I think I will have to subtract regular hours from total hours right ? For Total hours I .....


Go Back   UNP > Contributions > 24x7 Helpline

UNP

Register

  Views: 1998
Old 20-02-2014
Lily
 
Answered: How to Calculate Total hours and Overtime in Excel

Pleaseee I need formula for calculating total hours and overtime.

For overtime I think I will have to subtract regular hours from total hours right ?

For Total hours I tried subtracting end time from start time but it gave me wrong result :/


Name:  overtime,formula.JPG
Views: 2765
Size:  43.0 KB

(Reset Answer)Best Answer by jaswindersinghbaidwan

hmmmm

it will not provide you the value by simple minus formula, you need some editing also....

1. right click on columns of start time and end time. and go to format cells - click on time - from the list select the suitable format as per requirement .. e.g. 1:30:55 PM

2. Now select the total hours column and go to format cells again - click on custom (last option in the drop down list) - from the list select h: mm: ss .

3. in total hours column use the formula ... (End time - start time)

4. it will give you the total login/working hours..

5. after getting working hours, you may substract Total working hours from regular hours and it will give you overtime hours.. but make sure all cells are formatted to time format h: mm: ss
use this formula
=IF(F23<E23,E23-F23,"no overtime")

 
Old 20-02-2014
jaswindersinghbaidwan
 
Re: How to Calculate Total hours and Overtime in Excel

hmmmm

it will not provide you the value by simple minus formula, you need some editing also....

1. right click on columns of start time and end time. and go to format cells - click on time - from the list select the suitable format as per requirement .. e.g. 1:30:55 PM

2. Now select the total hours column and go to format cells again - click on custom (last option in the drop down list) - from the list select h: mm: ss .

3. in total hours column use the formula ... (End time - start time)

4. it will give you the total login/working hours..

5. after getting working hours, you may substract Total working hours from regular hours and it will give you overtime hours.. but make sure all cells are formatted to time format h: mm: ss
use this formula
=IF(F23<E23,E23-F23,"no overtime")
Attached Images
 

 
Old 20-02-2014
Lily
 
Re: How to Calculate Total hours and Overtime in Excel

*****

 
Old 20-02-2014
jaswindersinghbaidwan
 
Re: How to Calculate Total hours and Overtime in Excel

Originally Posted by Lily View Post
*****
ruko excel file email karta hun
which excel version r u using ?

 
Old 20-02-2014
Lily
 
Re: How to Calculate Total hours and Overtime in Excel

2010 !!

 
Old 20-02-2014
jaswindersinghbaidwan
 
Re: How to Calculate Total hours and Overtime in Excel

email sent on your ID..

 
Old 20-02-2014
Dhillon
 
Re: How to Calculate Total hours and Overtime in Excel

jb virus na bhej deyi kudi nu

 
Old 20-02-2014
jaswindersinghbaidwan
 
Re: How to Calculate Total hours and Overtime in Excel

Originally Posted by Dhillon View Post
jb virus na bhej deyi kudi nu


her problem is sorted out..


Reply
« dont copy/paste in remote disktop (help me) | Moto g »

Similar Threads for : How to Calculate Total hours and Overtime in Excel
* First Look Poster : Overtime [2012] *
How to Use Excel More Effectively: 10 Great Excel Tips & Tri
overtime
How To Calculate Your Pregnancy Due Date
How To Calculate Your Active Metabolic R

UNP