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?

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Nick Ison" wrote in message
...
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?

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" wrote in message
...
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?

a1= 2:45

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

--
Message posted from http://www.ExcelForum.com

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"mudraker " wrote in message
...
a1= 2:45

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


---
Message posted from http://www.ExcelForum.com/