Time Sheets

N

Nick Ison

I am currently working on time sheets for my employees and this is what I need to do; Allow users to enter their start time and finishing time, deduct breaks and give a final total. Is there a way to do this so that for example if you started at 12pm and ended at 2:45pm with no breaks it will be displayed as 2.75 hours rather than 2:45?
 
N

Norman Harker

Hi Nick!

Use:

=(B9-A9)*24
Format General

Times are recorded as a decimal part of a day so multiplying by 24
gives you hours.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
C

Chip Pearson

Nick,

Multiply your time value by 24 to convert 2:45 to 2.75. See
www.cpearson.com/excel/datetime.htm for lots more information about working
with dates and times in Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


Nick Ison said:
I am currently working on time sheets for my employees and this is what I
Click to expand...
need to do; Allow users to enter their start time and finishing time, deduct
breaks and give a final total. Is there a way to do this so that for example
if you started at 12pm and ended at 2:45pm with no breaks it will be
displayed as 2.75 hours rather than 2:45?
 
N

Norman Harker

Hi mudraker!

Usually the need for conversion is that the time is going to be used
in a subsequent calculation of wages etc.

You solution returns text. Although using =B1*2 will coerce your
formula to text, you'll still get problems with functions such as SUM
which will treat the return as zero.

So where there is a potential need for use in calculations:

=--(HOUR(A1)&"."&(MINUTE(A1)*100/60))

You'll still get problems if the formula is used on times in excess of
24:00 (formatted aggregates [hh]:mm). In that case your formula will
only return the modulus of the hours divided by 24. You could get
round this by incorporating a DAY in your calculations but...

Far easier and safer to multiply by 24.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top