# Microsoft Excel Problem

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

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:

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

thanks for all your help, it really helped

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

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?