New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Calculating Hours

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Calculating Hours

Postby mina milad » Wed Jun 29, 2016 4:16 am

Hello Everyone,

beginner question, :oops: might be too easy for people here but it has drove me crazy fro the past week, :oops:

my company started using puch in and out machine to keep track of employees and i want to keep track of my own working hours each month to calculate my Overtime.

the software provides only a report in the following formula

2016/05/12 09:06:53 in
2016/05/12 17:16:23 out

how do i format a cell to calculate how many working hours i have worked on that day ? :geek:

many thanks :)
  • 0

mina milad
Rookie
 
Posts: 1
Joined: Jun 29, 2016
Reputation: 0
Excel Version: 2010

Re: Calculating Hours

Postby gebobs » Wed Jun 29, 2016 9:58 am

Quite simply, use the following formula to convert the text to the date/time:
Code: Select all
=VALUE(LEFT(A1,19))


Then all you have to do is subtract the start time from the end time. Of course, the resulting number will be a fraction of a day, in this case, 0.34. To convert it to hours, just multiply by 24 to get 8.16 hours. Otherwise, you can format it as "h:mm" and it will display as 8:09 (8 hours and 9 minutes).
  • 0

You do not have the required permissions to view the files attached to this post.
gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Calculating Hours

Postby Kevin UK » Wed Jun 29, 2016 10:05 am

Hi

With out an example workbook, if the data in your post is in A1 & A2. I would use text to columns, delimited, space as the delimiter, destination $A$1 and do not import the last column. That will put the dates in column A and the times in column B. You can then use the MOD function.
Code: Select all
=MOD(B2-B1,1)
  • 1

Regards

Kevin
Kevin UK
Excel Master
 
Posts: 568
Joined: Jun 24, 2012
Reputation: 86
Excel Version: 2016


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 50 guests

cron