dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
9666
share rss forum feed

JoelC707
Premium
join:2002-07-09
Lanett, AL
kudos:5

[Excel] Time sheet hours worked formula

Click for full size
I have a time sheet I fill out at work, it's a basic excel spreadsheet and I wanted to automate it some. So I went online and found a formula that I plugged in to the "hours" column that are supposed to calculate hours worked based on the in/out times provided in other fields. It works great, except when it encounters 10:00 or 11:00 (AM/PM doesn't seem to matter).

This is the formula I'm using as it exists in one of the fields:
=((C24-B24+(C24<B24))*24)+((E24-D24+(E24<D24))*24)

I've also attached a screen shot showing various sample in/out times and it's resulting calculations. Can anyone tell me what's wrong with the formula and how I can correct it?


mjhouser
Premium
join:2001-10-06

downloadSample.xlsx.zip 11,321 bytes
I did this for a friend a while back. A simple in/out in/out time sheet. He enters his time XX:XX A or P.

Format the cells for time.

This does not answer or fix your question... but it's simple.


mjhouser
Premium
join:2001-10-06
reply to JoelC707

Click for full size
I took a look at your formula and it seems to work fine, both 12 and 24hr formats on the same sheet. It's late maybe I am missing something.

JoelC707
Premium
join:2002-07-09
Lanett, AL
kudos:5

Thanks for the look. I went back and forth on various cells that "work" and "don't work" and there's no fluke. I adapted the formula to include the extra set of in/out fields and then just drug it down the column to the other fields. Everything seemed fine and it even handled a change to a different day just fine (starting at say 8 PM and ending at 2AM correctly reported 6 hours).

I don't know what is/was up with the formula. I have used the same sheet mostly from day 1 with only minor changes in it (such as adding the tasks completed field). I can remember not long after I started, the address at the top of the spreadsheet which has my bosses address, was "corrupt" of sorts. It was a weird combination of his address and a co-workers address. Maybe there is just some fluke and I need to "refresh" the sheet by recreating some fields or even recreating it entirely?



mjhouser
Premium
join:2001-10-06
reply to JoelC707

downloadYour File.xlsx.zip 9,989 bytes
Not sure. Here is a like copy of your file that works. Tweak it with your heading and to your taste.