Software

Ask something
Discussion Locked

Excel formula for equating time.

0 helpful
8 replies

by dlech 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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!

8 replies

by peyison

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.

by shanghai sam

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

by c_m_s

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

This discussion is closed to new replies.