How do you calculate formulas with time as the format?

  • Thread starter Thread starter Amey
  • Start date Start date
A

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?
 
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
 
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.
 
to calculate a time worked by an hourly rate in B2
multiply the time worked by 24
=C2 * 24 * B2
Click to expand...

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
 
Back
Top