Trying to Create Timesheet w/ Drop Down List for Start/End time & Calculating Hours

[Swoodward]

I'm trying to create a time-sheet in excel, where my employees will only have to select an in/out time and the sheet will calculate their hours as a decimal. I know how to create a drop down list for each field (Start / End). I structured the list to allow 6 minute increments to be selected, ex 8:00, 8:06, 8:12, etc. Its formatted so that AM and PM are displayed correctly as well, and I formatted PM times as 13:00 for 1pm.

My problem is that I cant think of a correct formula to calculate that a start time of 8:00AM and an end time of 4:12PM would equal 8.2 hrs. Right now, the formula I am showing gives me 24.20 .

Formula: =INT(C14)*24+HOUR(C14)+ROUND(MINUTE(C14)/60,2)-INT(B14)*24+HOUR(B14)+ROUND(MINUTE(B14)/60,2)

C14 is end time, B14 is start time.

Simply taking C14-B14 yields .34 in number format. It yields 8:12AM in time format.

 

[Snakehips]

Swoodward,

Welcome to MrExcel.

Try... =(C14-B14)*24

The C14-B14 gives the difference between the times as a decimal fraction of 1 day (24hrs) so multiply by 24 to get hrs.

Hope that helps.

 

[Swoodward]

Swoodward,

Welcome to MrExcel.

Try... =(C14-B14)*24

The C14-B14 gives the difference between the times as a decimal fraction of 1 day (24hrs) so multiply by 24 to get hrs.

Hope that helps.

Snakeships, that worked perfectly. I thought I had a work around of taking the small result, hiding that column, and then using a formula to display it as 8.20. Your multiply by 24 solution helps me avoid that. Thanks!