Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 25th 06, 01:51 PM posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.newusers
 
Posts: n/a
Default 24 time format and calculations

I have a spreadsheet with start and end times for people's working day,
in 24 hour format. So it will be 830, 900, 1030, 715 etc in the start
column and 1700, 1715, 1550 etc in the end column. I would like to do
a calculation on these times to work out for each person how many hours
are in their working day (including lunch and breaks). At the moment
Excel regards these as regular numbers, so I cannot do a calculation
like 1700-830 because it won't come out right. I have tried fiddling
with the cell format with no success. How do I do it?


  #2   Report Post  
Old January 25th 06, 02:34 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 24 time format and calculations

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

and format as time

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

wrote in message
oups.com...
I have a spreadsheet with start and end times for people's working day,
in 24 hour format. So it will be 830, 900, 1030, 715 etc in the start
column and 1700, 1715, 1550 etc in the end column. I would like to do
a calculation on these times to work out for each person how many hours
are in their working day (including lunch and breaks). At the moment
Excel regards these as regular numbers, so I cannot do a calculation
like 1700-830 because it won't come out right. I have tried fiddling
with the cell format with no success. How do I do it?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time calculations and additional 24 hour days Mark G Excel Worksheet Functions 2 December 29th 05 08:39 PM
Convert data into standard military time format geog Excel Discussion (Misc queries) 2 December 12th 05 07:46 PM
Calculations from date and time values kp Excel Worksheet Functions 7 November 27th 05 08:07 PM
convert time imported as text to time format for calculations batfish Excel Worksheet Functions 3 October 27th 05 11:24 PM
Please help with Time calculations Al Excel Discussion (Misc queries) 2 August 14th 05 07:41 PM


All times are GMT +1. The time now is 12:13 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017