General discussion

Locked

Excel formula for equating time.

By dlech ·
What is the formula for figuring out time? I am creating a time sheet and I want it to automatically figure out the hours per day that I work. I have entered a few different formulas but they are not giving me the results I want. I always end up with .08 hours instead of 8 hours. The formula I am trying to write is - Start Time to Lunch Time, then After Lunch to Quitting Time. I also need a formula for overtime. Something saying if the regular time in more than 8 hours then.... Any help is greatly appreciated. Thanks!

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel formula for equating time.

by DKlippert In reply to Excel formula for equatin ...

Mostly you need to multiply by 24.
Here is a URL of a discussion about Time card math:
http://www.cpearson.com/excel/overtime.htm

Collapse -

Excel formula for equating time.

by dlech In reply to Excel formula for equatin ...

The question was auto-closed by TechRepublic

Collapse -

Excel formula for equating time.

by Peyison In reply to Excel formula for equatin ...

You should be able to do this pretty easily if you enter the times as actual times and make sure the cells are formatted as "Time".

If you enter 10:00 into a cell, Excel will format the cell as Time, and convert 10:00 to the equivalent number inTime format. This is basically a fraction that represents the percentage of the specified time in a 24 hour day (really probably the number of seconds (or smaller) over the number of total seconds in a day. So if you enter 10:00 AM in a cell, the actual value in the cell is .416667, which is 10/24. You can see this be changing the format of the cell to General.

So to do what you're looking for - You can enter times for:

*Start Day
*Start Lunch
*End Lunch
*End Day

And total time worked is:
(Start Lunch - Start Day)+(End Day - End Lunch)

Make sure you include "PM" for PM time, like: 1:00 PM or enter it as military time: 13:00.

To calculate overtime, subtract .333333 from Total Time. .33333 (8/24) is the same as saying 8hours. You could also enter 8:00 in a cell, then subtract that cell from the Total time since "8:00" will be converted to Time and the actual value in the cell will be .333333.

Hope this helps.

Collapse -

Excel formula for equating time.

by dlech In reply to Excel formula for equatin ...

The question was auto-closed by TechRepublic

Collapse -

Excel formula for equating time.

by Shanghai Sam In reply to Excel formula for equatin ...

Hi there,
May have an answer to your question.

Set up an array something like this:

Mon Tue Wed Thu Fri
StrtDay
StrtLnch
EndLnch
EndDay
Total

At the bottom of each column you will do a sum of that day. Formula is =(StrtLnch-StrtDay) + EndDay-EndLnch)
Select a cell to hold the weekly sum. Format this cell by right clicking|Format Cells|Number|Time|37:30:55 (That is what mine says). Enter a formula to sum the daily totals.
Example =Sum(Mon Total:Fri Total).

Select a second cell to hold a constant value, enter 40:00:00, and format it the same as the weekly sum.

Finally select a cell to hold any overtime value, subtract the constant value from the weekly sum, and ensure the format of this cellis the same as the other two (it should be by default).

This should take care of you.

Note: Pressing Shift + Ctrl + : (colon) at the same time will automatically enter the current time for you.

Luck,
Mike

Collapse -

Excel formula for equating time.

by C_M_S In reply to Excel formula for equatin ...

Hi there,
May have an answer to your question.

Set up an array something like this:

Mon Tue Wed Thu Fri
StrtDay
StrtLnch
EndLnch
EndDay
Total

At the bottom of each column you will do a sum of that day. Formula is =(StrtLnch-StrtDay) + EndDay-EndLnch)
Select a cell to hold the weekly sum. Format this cell by right clicking|Format Cells|Number|Time|37:30:55 (That is what mine says). Enter a formula to sum the daily totals.
Example =Sum(Mon Total:Fri Total).

Select a second cell to hold a constant value, enter 40:00:00, and format it the same as the weekly sum.

Finally select a cell to hold any overtime value, subtract the constant value from the weekly sum, and ensure the format of this cell is the same asthe other two (it should be by default).

This should take care of you.

Note: Pressing Shift + Ctrl + : (colon) at the same time will automatically enter the current time for you.

Luck,
Mike

Note: This a duplicate post. My ISP kicked me out whilE composing the above answer.

Mike

Collapse -

Excel formula for equating time.

by dlech In reply to Excel formula for equatin ...

The question was auto-closed by TechRepublic

Collapse -

Excel formula for equating time.

by dlech In reply to Excel formula for equatin ...

This question was auto closed due to inactivity

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums