Hi Scooterdog!
For first question see the answers already given in your separate
question. Best keep to one question per post.
A fuller explanation than most will give on your time question but I
hope it clears up the conceptual problems of dealing with time:
Where differences between times are concerned we get a problem if the
times span midnight. A simple B1-A1 returns an error of ##### or if
the 1904 Date System is used you get a negative time.
Simple deductions (with or without resolving the spanning of midnight
issue) are not appropriate because Excel uses a date / time serial
number system where the time is the decimal element of the number and
thus represents a decimal part of one day.
The following return time as numbers as opposed to time formatted
numbers. You need to pre-format the cells as Numeric or change the
format from time to numeric or General
With:
A1 21:30 and B1 06:45
Hours:
=(B1-A1+(B1<A1))*24
Returns: 9.25
Minutes:
=(B1-A1+(B1<A1))*(24*60)
Returns: 555
Seconds:
=(B1-A1+(B1<A1))*(24*60*60)
Returns: 33300
The structure (B1<A1) in this context can be described as an implicit
IF function which returns 1 if True and 0 if False. Since time is
measured as a decimal of a day, the addition of 1 serves to calculate
the difference between 1 + B1 and A1 which is what you want. In the
case of minutes and seconds calculations the conversion from decimal
part of a day has not been simplified to 1440 and 86400 as this makes
understanding of the logic more easy.
Using an explicit IF function, you would use:
=(B1-A1+IF(B1<A1,1,0))*24
Returns: 9.25
=(B1-A1+IF(B1<A1,1,0))*(24*60)
Returns: 555
=(B1-A1+IF(B1<A1,1,0))*(24*60*60)
Returns: 33300
The following return the answer in time serial numbers representing
decimal parts of a day.
=B1-A1+(B1<A1)
OR:
=B1-A1+IF(B1<A1,1,0)
In both cases we can choose the time format to be displayed.
hh:mm:ss displays 09:15:00
h:mm:ss display 9:15:00
[mm]:ss display 555:00
[ss] displays 33300
For time generally see:
Chip Pearson:
http://www.cpearson.com/excel/datetime.htm#AddingTimes
Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/datetime.htm
Go to their links to their page indexes at the bottom of those pages
and you should bookmark them.
For timesheets generally see:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Dave McRitchie
http://www.mvps.org/dmcritchie/excel/datetime.htm
With a downloadable employee timesheet available at:
John Walkenbach:
http://j-walk.com/ss/excel/files/timesht.htm
The John Walkenbach reference shows a pretty picture of the output and
the downloadable version has an unprotect sheet option button that
allows you to see all the essential formulas in operation. You will
need to have security settings on Medium or Low before you can open a
fully operational version as there is some VBA code. That code is
viewable but not really necessary in terms of your being able to see
how the formulas work (and it has value in it's own right as an
example of some VBA code techniques).
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Sunday 27th July: Cuba (Revolution Day),
Djibouti (Independence Day), Hong Kong (Lu Pan Day), North Korea
(Victory Day), Puerto Rico (Barbosa Day), Russian Federation (Navy
Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
