Microsoft Excel Problem

  Adz1310 21:23 05 Mar 2006
Locked

Hey guys,
Just got a niggle with excel. I am doing some work, and have got a spreadsheet with times in it such as 1.38, 2.45, 2.50 etc etc. They are all meant to be times such a 1 minute 38 secs. though cannot seem to manipulate them like this. I want to find the difference between these times though it gives me answers such as 1.84 which isnt a time, is there any formulas I can use to allow them to be in minutes and seconds. I have tried the format button but cant seem to get anywhere!!!
Please somebody help, need to get this work done!!!

  VoG II 21:33 05 Mar 2006

You need to enter times in this format

11:30

otherwise Excel will treat your entries as numbers. You should be able to use Edit>Replace to change all . to : then make sure all relevant cells are formatted as time.

If you have cells where the results of calculation will be greater than or equal to 24h then you need to select those cells and apply a custom format:

[h]:mm

  Adz1310 23:50 05 Mar 2006

cheers pal, that worked a treat, just another quick question, lets see if anyone or even yourself knows the answer.
I have my times now such as
02:20, 04:45 etc.

Is there a formula that you can type in that will give you those answers in seconds.

so for 2:20 it would be 140 seconds?

  beynac 08:50 06 Mar 2006

Wity the time in cell A1, convert the time into a number of hours (decimal) with this formula in cell A2: =(A1-INT(A1))*24

Then in A3 convert the hours into minutes "=CONVERT(A2,"hr","mn")" or seconds "=CONVERT(A2,"hr","sec")".

I don't know if these could be nested into one formula.

  beynac 08:51 06 Mar 2006

Oops! "Wity" should be "With".

  beynac 08:57 06 Mar 2006

You can nest it: =CONVERT(((A1-INT(A1))*24),"hr","mn")

  beynac 16:56 06 Mar 2006

I should have mentioned that you need the Analysis ToolPak add-in installed for the "convert" function to work. To install:

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.

  Adz1310 17:10 06 Mar 2006

thanks for all your help, it really helped

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone