time formula question...

G

Greg

what is the formula for adding time?
For example, I would like my timesheet to automatically add the time I
worked by
adding the time I arrived to the time I left and put the amount of hours in
a total
column. In other words I would like to enter 9:00 AM and 5:00 PM and have
the
total show 8.

thanks in advance
Greg
 
P

Peo Sjoblom

=end_time - start_time

format as time or use

=(end_time - start_time)*24

and format as general
 
R

Ron Rosenfeld

what is the formula for adding time?
For example, I would like my timesheet to automatically add the time I
worked by
adding the time I arrived to the time I left and put the amount of hours in
a total
column. In other words I would like to enter 9:00 AM and 5:00 PM and have
the
total show 8.

thanks in advance
Greg
Click to expand...

Assuming you don't work past midnight, then:

=EndTime - StartTime

and format the result as h.

If you want hours and minutes, then format the result as something like:

h:mm


--ron
 
L

Linuxster

I hope that I answer this correctly. I have to keep track of events for 7
different processes at work. We are a 24 hour facility except for weekends.
I then have to create Gantt charts for the week showing concurrancies of
events for the processes.

My point is, that after some research, I realized that it was very important
to understand how Excel stores Date/Time data, especially if the start/end
times 'cross midnight'.

Excel 'increments' time from 1/1/1900. The time of day is stored (even
though it may not be presented based on format settings) as a decimal value
between 0 and 0.99999999 or 1(set the format to General, you'll see!). The
date is stored as a whole number of 1(representing a 24 hour period) and
inremented from 1/1/1900.

So the date time that I write this (as Excel stores it) is 38407.73069

Meaning 38,407 days after 1/1/1900 and .73069(5:30PM or 17:30) of the said
38,407 day.

This is important to know, because if you need to track time 'crossing
midnight' or over several days, what you really need to do is (formula-wise),
is SUBTRACT time, not ADD it, because it all based/stored as a decimal value
RELATIVE to 1/1/1900 or a past 'event'

Today, 2/23/2005 is Decimal 38407
Yesterday, 2/22/2205 was Decimal 38406 and any time between that 24 hour
period was a DECIMAL value between 0 and 1

One will almost always have to SUBTRACT the 'end' date/time from the 'start'
date/time because the end time is a larger number than the start
time(regardless of the fact that Bankers start at 10:00am and go home at
2:00pm or not. That is not 8 hours. LOL!

Cheers!
 
K

Keely

I have a follow-up to the original question. What if I need to subtract time
out of the elapsed time. Specifically, if an employee works more than 6
hours, then he/she will take 30 minutes for lunch. So the correct "hours
worked" for a 9:00 AM to 5:30 PM shift is 8 hours, not 8 hours and 30
minutes. I can write the logic for the if/then statement, but I cannot
determine how to indicate that 30 minute subtraction.

Thanks!
~keely
 
L

Linuxster

This doesn't 'answer' your question, but it may help.
Microsoft has some cool templates that you might be able to use right 'out
of the box' or use to 'study'.

http://office.microsoft.com/en-us/templates/CT011377091033.aspx

Or, to navigate there manually, go their templates, microsoft Office
Programs, Excel, Business, then Payroll.

The very last template there 'Weekly Work Schedule' is what I often use.

Good Luck!
 
Top