excel formula help

Soldato
Joined
11 Oct 2005
Posts
5,715
Location
Derbyshire
Hi, cannot seem to get my head round the following. perhaps some one can help with the formula for Excel.

here is what i want

basically. in what cell time you arrive at work. another cell time you leave work. then a way of adding those cells up.

eg:
A1........................B1................C1
Start Time........Finish Time.....Total Hours
8.30am...............3.30pm...........7 hours




Thank you people :)
 
I'm not saying this is the neatest way but this will convert the text time in cell A1 to a decimal time:

Code:
=(LEFT(A1,FIND(".",A1)-1) + IFERROR(IF(FIND("pm",A1)>0,12,0),0))+(MID(A1,FIND(".",A1)+1,2))/60

just copy that to cell d1 (which will than covert the text time in cell b1) then d1-c1 gives you your total hours. Will work providing the times are entered as x.xx[am/pm]

eg:
CKLVEFr.png
 
Last edited:
If you're entering these times manually you could simply use a time format that Excel recognizes (such as hh:mm) then use the formula joeyjojo gave.
 
Just set the format as time HH:MM:SS (under the time category) for the input boxes and [H]:mm:ss for the output box and do the equation Joey said.

Job done if you want the results in hours and minutes.
 
Back
Top Bottom