Formula to get no of hours

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Hi,

May be simple for all
but I am not getting it

I have IN TIME and OUT TIME everyday for employees.
If each employee is working for 12 hrs everyday.
How will I get total hours at the end of the month

Please help me
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Nothing Yet

I am entering 9.30 in IN TIME cell and 20.30 in OUT TIME cell and after 31 day in the last column I want Total No Of Hours Worked in a month
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
The formula would be Out Time - In Time , e.g. = B1-A1

To get the total would then be something like =SUM(C1:C31) formatted as custom format [h]:mm

Dom
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

If your times are in columns A and B starting in row 1, in C1 enter:

=MOD(B1-A1,1)

and copy down to C2:C31. In C32 enter:

=SUM(C1:C31)

and format as [hh:mm] for elapsd time. If you want the time in decimal hours you can use:

=SUM(C1:C31)*24
 

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
When I enter 12.30 in the cell it shows 12-01-1900 7:12:00 AM in the formula bar why doesn't it show only 12:30 in the cell.

and I don't have a row for everyday hours total i want it in the last cell itself
 

ramananhrm

Board Regular
Joined
Apr 19, 2011
Messages
156

ADVERTISEMENT

Cell A2 = 9:30
Cell B2 = 20:30

Enter the below mentioned function in Cell C2 (Change the format of the cell)


=MOD(B2-A2,1)*24


Use sum function to add all the hrs in 'C' column.

 
Last edited:

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Time should be entered with a : not a . like 12:30

Dom
 

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
Thank you all for your help I got it as I was entering wrongly as 12.3 and the MOD formula worked for me.

Thank You very much
 

jackt05

Board Regular
Joined
Feb 1, 2009
Messages
194
I am getting one more problem related to this

When I write Absent in Any IN TIME cell as to employee is not coming that day the formula =MOD(B1-A1,1) the answer below is not coming
it show #Value!

Please help
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,515
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top