How do you calculate formulas with time as the format?

[Amey]

I need some help out there. I can add with time as the
format, but I cannot perform any other type of formula.
I am trying to create a Time Clock Spreadsheet. Anyone
that can help me out there?

 

[David McRitchie]

Hi Amey,
You should provide examples of what you cannot do.

to subtract times that may run through midnight
The real solution is to include the date & time, but if you
only have the time component.
start time in C2: 22:00
end time in D2: 05:00
use formula =D2-C2 + (C2>D2) format as time
or for a number of hours multiply by 24 and format as a number.

to calculate a time worked by an hourly rate in B2
multiply the time worked by 24
=C2 * 24 * B2

to find out

Dates are stored as days after Dec 31, 1899 and time is
stored as a fraction of a day, so that both can be stored in
a cell and internally takes no more bytes to record both.

More information on Date and Time and where to find Time Sheet
Information can be found in
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm Chip Pearson
http://www.j-walk.com/ss/excel John Walkenbach
Chip Pearson and John Walkenbach each have downloadable
example spreadsheets.

A downloadable employee time sheet can be found in John
Walkenbach's http://www.j-walk.com/ss/excel/files/index.htm

Post back if you have any more questions on this, or even better
use Google Groups to find previously answered questions.
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100
more information on Google Groups on my site
http://www.mvps.org/dmcritchie/excel/xlnews.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Norman Harker]

Hi Amey!

Sorry to pass you on but there's quite a lot involved.

Try downloading the Employee Time Sheet from:

John Walkenbach:
http://j-walk.com/ss/excel/files/general.htm

Then there's a whole load of stuff on dates and times at:

Chip Pearson:
http://www.cpearson.com/excel/topic.htm

That index page will give you lots of time and date related links.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David McRitchie]

to calculate a time worked by an hourly rate in B2
multiply the time worked by 24
=C2 * 24 * B2

the correction is:

if E2 contains the number of hours worked
from the formula =D2-C2 + (C2>D2)
then the above formula should be
F2: =E2*24 * B2