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

[tonywatsonhelp]

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????

 

[SQUIDD]

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]

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

 

[tonywatsonhelp]

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]

No happy to use 24 hour clock

 

[AliGW]

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.

 

[tonywatsonhelp]

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

thanks very much

Tony

 

[AliGW]

You're welcome, Tony!

 

[Snakehips]

A bit late but I got sidetracked.

Another possibility...

Excel Workbook
	A	B	C
1	In	Out	Hours.Min
2	0	24	24
3	8.15	16.45	8.3
4	9.15	10.55	1.4
5	3.43	4.01	0.18
6	9.45	10.65	1.2
Sheet5

 

[AliGW]

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