Simple time sheet

  • Thread starter Thread starter Graham Savell
  • Start date Start date
G

Graham Savell

I want to operate a simple time shet that will convert time into a decimal
number i.e

Time started 5.15 pm [hours,
minutes]
Time finished 7.30 pm [hours,
minutes]
Elapsed time 2.15 [hours,
minutes]

Equals 2.25 hours

Is it also possible to make the formula work from morning to afternoon -
i.e. 11.15am to 1.30pm = 2 hours 15 minutes = 2.25 hours

Thanks in advance
 
Graham,

Your post shows the *times* with decimal points and lower case letters.
These are therefore text entries NOT times. To enter times in AM/PM format
either pre-format the cells in that format or enter the times with a full
colon, a SPACE then AM or PM. (In point of fact as long as the time is
entered with a full colon and there is a space between it and the AM/PM, XL
will accept lower case am or pm and capitalise it.)

To get the elapsed time try =<end time>-<start time> and format the cell as
h:mm

To change the time into decimal time to use it in calculations multiply the
time by 24 and format the cell as General, (XL will "helpfully" format it as
tme for you and you have to change it.)

For all that you would ever want to know about times got to:

http://www.cpearson.com/excel/overtime.htm

HTH

Sandy
 
wOOF! That's an excellent Public Service section! Thanks.


Sandy Mann said:
Graham,

Your post shows the *times* with decimal points and lower case letters.
These are therefore text entries NOT times. To enter times in AM/PM
format
either pre-format the cells in that format or enter the times with a full
colon, a SPACE then AM or PM. (In point of fact as long as the time is
entered with a full colon and there is a space between it and the AM/PM,
XL
will accept lower case am or pm and capitalise it.)

To get the elapsed time try =<end time>-<start time> and format the cell
as
h:mm

To change the time into decimal time to use it in calculations multiply
the
time by 24 and format the cell as General, (XL will "helpfully" format it
as
tme for you and you have to change it.)

For all that you would ever want to know about times got to:

http://www.cpearson.com/excel/overtime.htm

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


Graham Savell said:
I want to operate a simple time shet that will convert time into a
decimal
number i.e

Time started 5.15 pm [hours,
minutes]
Time finished 7.30 pm [hours,
minutes]
Elapsed time 2.15 [hours,
minutes]

Equals 2.25 hours

Is it also possible to make the formula work from morning to afternoon -
i.e. 11.15am to 1.30pm = 2 hours 15 minutes = 2.25 hours

Thanks in advance
Click to expand...
Click to expand...
 
<<"entered with a full colon and there is a space between it and the AM/PM,
XL
will accept lower case am or pm and capitalise it.)">>
Click to expand...

More accurately,
<space>a
<space>p
Is *also* acceptable.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Sandy Mann said:
Graham,

Your post shows the *times* with decimal points and lower case letters.
These are therefore text entries NOT times. To enter times in AM/PM format
either pre-format the cells in that format or enter the times with a full
colon, a SPACE then AM or PM. (In point of fact as long as the time is
entered with a full colon and there is a space between it and the AM/PM, XL
will accept lower case am or pm and capitalise it.)

To get the elapsed time try =<end time>-<start time> and format the cell as
h:mm

To change the time into decimal time to use it in calculations multiply the
time by 24 and format the cell as General, (XL will "helpfully" format it as
tme for you and you have to change it.)

For all that you would ever want to know about times got to:

http://www.cpearson.com/excel/overtime.htm

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


Graham Savell said:
I want to operate a simple time shet that will convert time into a decimal
number i.e

Time started 5.15 pm [hours,
minutes]
Time finished 7.30 pm [hours,
minutes]
Elapsed time 2.15 [hours,
minutes]

Equals 2.25 hours

Is it also possible to make the formula work from morning to afternoon -
i.e. 11.15am to 1.30pm = 2 hours 15 minutes = 2.25 hours

Thanks in advance
Click to expand...
Click to expand...
 
You're quite right RD. I never use the AM/PM format and I had forgotten
that XL was the helpful.

Regards

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

Ragdyer said:
<<"entered with a full colon and there is a space between it and the AM/PM,
XL
will accept lower case am or pm and capitalise it.)">>
Click to expand...

More accurately,
<space>a
<space>p
Is *also* acceptable.
--
Regards,

RD

-------------------------------------------------------------------------- -
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------------------------- -
Sandy Mann said:
Graham,

Your post shows the *times* with decimal points and lower case letters.
These are therefore text entries NOT times. To enter times in AM/PM format
either pre-format the cells in that format or enter the times with a full
colon, a SPACE then AM or PM. (In point of fact as long as the time is
entered with a full colon and there is a space between it and the AM/PM, XL
will accept lower case am or pm and capitalise it.)

To get the elapsed time try =<end time>-<start time> and format the cell as
h:mm

To change the time into decimal time to use it in calculations multiply the
time by 24 and format the cell as General, (XL will "helpfully" format
Click to expand...
it
as
tme for you and you have to change it.)

For all that you would ever want to know about times got to:

http://www.cpearson.com/excel/overtime.htm

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


Graham Savell said:
I want to operate a simple time shet that will convert time into a decimal
number i.e

Time started 5.15 pm [hours,
minutes]
Time finished 7.30 pm [hours,
minutes]
Elapsed time 2.15 [hours,
minutes]

Equals 2.25 hours

Is it also possible to make the formula work from morning to afternoon -
i.e. 11.15am to 1.30pm = 2 hours 15 minutes = 2.25 hours

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top