Excel question (Posted here so more folks will see it)

[SK6]

Does anyone know how to calculate times in excel, such as time clock data?

i.e.:

09:10 PDT
11:09 PDT
11:19 PDT
12:59 PDT

total hours worked

[Da Peach]

The excel help file lays it out well. Do a search on "calculate time"

[Potroast88]

Please speak English from now on.

[LordOpie]

I don't know what the help file says, but you could convert to decimal and go that route?

[ire]

http://www.cpearson.com/excel/overtime.htm

[binary visions]

Dude, there's a whole section of the help file called "How Excel stores dates and times" and there are approximately six hundred and fourty-seven thousand pages that a Google search turns up on the subject :p

[SK6]

Dude, there's a whole section of the help file called "How Excel stores dates and times" and there are approximately six hundred and fourty-seven thousand pages that a Google search turns up on the subject :p

Yeah, I know....However, without divulging client information, which is REAL specific, I was generalizing. :D

However, this post can be moved to the appropriate section. :thumb:

[SK6]

Thanks all!

[johnbryanpeters]

+ and - work on time just like they do on other numerics. You probably have to take out the PDT.

[SK6]

That I did, but the time areas that were negative times showed up as #######, and I checked the formating if the column. So I figured I'd have to create a column sort for just those instances, reformat the formula and go from there.

[binary visions]

If something shows up as ###### it's because the column isn't wide enough. Double click on the right hand side of the column header to auto-expand it.

[ultraNoob]

Format Cells to "time" (military time works best, 13:30 = 1:30 PM)
Subtract Stop time from Start time and there you go

http://i70.photobucket.com/albums/i83/shermans814/TimeCalc.jpg

[BigMike]

Shouldn't it be in Eastern Time?

[viper2pt0]

If something shows up as ###### it's because the column isn't wide enough. Double click on the right hand side of the column header to auto-expand it.

While generally this is correct, it does not apply in this scenario. The reason being is that by default, excel uses the 1900 date format, which does not allow negative times. In order to properly display negative times you must change it to the 1904 format. To do this:
Tools --> Options --> Calculation --> 1904 Date System
This will let your negative times show properly.

[SK6]

While generally this is correct, it does not apply in this scenario. The reason being is that by default, excel uses the 1900 date format, which does not allow negative times. In order to properly display negative times you must change it to the 1904 format. To do this:
Tools --> Options --> Calculation --> 1904 Date System
This will let your negative times show properly.

And THAT was the answer I was looking for! Thanks! Ever in the Richmond area, give me a shout, beers for sure!

I already owe BV almost an entire brewery! ;)

[binary visions]

While generally this is correct, it does not apply in this scenario. The reason being is that by default, excel uses the 1900 date format, which does not allow negative times. In order to properly display negative times you must change it to the 1904 format. To do this:
Tools --> Options --> Calculation --> 1904 Date System
This will let your negative times show properly.

Awesome. Thanks for the info.