Inputing worktimes, keeping it simple but getting hours worked???

tonywatsonhelp

Well-known Member
Hi Everyone,

Thought this would be easy but struggling,

I want to set up a time sheet with Start Time, End Time, Hours Worked, in columns A, B, C,

here are the problems I'm getting.

If I format the Start and end cells as "H:mm" I get the date and time, so I type in start time as 9.30 I get "09-01-1900 7:12:00 AM"

so heres my first question,

how do I set up excel so we cann iput time using a false stop like "9.30" or even just "9" as there are a lot of times to input so it has to be quick?

and if I just got then to type it in as "9.30" formatted as a number is there a why to get the number of hours calculated from say "9.45" to "5.30"

this might be easy but I cant get it to work????

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

SQUIDD

Well-known Member
i done something similar recently

in the end i simply entered it in 24 hour clock format. so 9.45 and 17.3.

so i will be intrested to see other responses also

AliGW

Banned
How are you expecting Excel to distinguish between 5.30AM and 5.30PM?

tonywatsonhelp

Well-known Member
Hi Squidd,

yes that's fine but we are calculating decimal points to 100 not 60 so "9.15" to "9.45" shows as 0.30 which is great but if I do "9.15" to "10.55" I get "1.70" instead of "2.10" which is what it is in minutes???? I guess the simplest way is does anyone have a formula to calculate hours and minute from to decimal numbers????

tonywatsonhelp

Well-known Member
No happy to use 24 hour clock

AliGW

Banned
There is probably a more efficient formula, but I believe this works:

=INT(B1-A1)+(IF(60-((A1-INT(A1))*100-(B1-INT(B1))*100)>=60,60-((A1-INT(A1))*100-(B1-INT(B1))*100)-60,60-((A1-INT(A1))*100-(B1-INT(B1))*100)))/100

where A1 is your start time and B1 your end time and both are formatted as numbers to two decimal places.

Last edited:

tonywatsonhelp

Well-known Member
Thank Aligw, this will be a great help :-0

thanks very much

Tony

AliGW

Banned
You're welcome, Tony!

Snakehips

Well-known Member
A bit late but I got sidetracked.

Another possibility...
Excel Workbook
ABC
1InOutHours.Min
202424
38.1516.458.3
49.1510.551.4
53.434.010.18
69.4510.651.2
Sheet5

AliGW

Banned
There's a function I'd never heard of before! Like it ...

Replies
1
Views
77
Replies
0
Views
95
Replies
5
Views
100
Replies
0
Views
261
Replies
2
Views
143

1,191,718
Messages
5,988,267
Members
440,146
Latest member
rgomes8

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.

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

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