If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Cells with time format and calculating the diffrence
Oh Yes! Down under is into the 1st Jan ahead of you all at 1540 hours.
"Peo Sjoblom" wrote: Thanks for the feedback I assume you already had your celebration -- Regards, Peo Sjoblom (No private emails please) "MikeR-Oz" wrote in message ... Cheers Peo- That has solved it for me - fatastic. Have a great New Year. Mike "Peo Sjoblom" wrote: You need to enclose the whole formula in parentheses like in =(TEXT(P8,"00\:00")-TEXT(O8,"00\:00"))*1440 after that it is important to format as general or number, NOT time -- Regards, Peo Sjoblom (No private emails please) "MikeR-Oz" wrote in message ... And could I * the cell c1 by 1440 ? as in =TEXT(P8,"00\:00")-TEXT(O8,"00\:00")*1440 I have tried and I know this is not correct - what is missing? Mike "Peo Sjoblom" wrote: One way, =TEXT(B1,"00\:00")-TEXT(A1,"00\:00") format as [hh]:mm will return 00:45 if you want minutes as integers multiply the result with 1440 and format as General -- Regards, Peo Sjoblom (No private emails please) "MikeR-Oz" wrote in message ... Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30 at all and just be able to type 1330 as I knw I mean it as 24 hour time -SO LONG AS - the minutes calculated in the third cell is the difference between the first two times. so again I want to type 1330 in A1 and 1415 in B1 and have the C1 calculate the difference as minutes? How may I do this? Happy New Year Mike "Stefi" wrote: „MikeR-Oz” ezt *rta: I have formatted 2 cells say A1 and B1 as time and chose the first type being in the form 13:30 - This shows up in the equation line as 1:12:00 PM. So I type in 13:12 and I get what is required BUT is there a way that I can type 1312 without having to put the ':' in? and still get it to produce the time equation format for my 3 rd cell C1 which will be to calculate the minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the 3rd cell as 0:08 . I have used =+B1-A1 for the C1 cell. Use an extra input column (say Z) and format it as text, and apply this formula in your time column A and B: =TIMEVALUE(LEFT(Z1,2)&":"&RIGHT(Z1,2)) If you enter "1312" in Z1 then A1 will show 13:12 as a real XL time value. Also the other thing is that when I wish to calculate the minutes between the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to have the minutes i.e 60. How can I format this or do I need to write a script/ macro of some sort? Use custom format [mm] Regards, Stefi |
#12
|
|||
|
|||
Cells with time format and calculating the diffrence
Normal paste makes grey shaded area if content is copied from the net (I
don't know why). Use PasteSpecial/Text instead! Regards, Stefi „MikeR-Oz” ezt *rta: Thats brilliant Peo- I appreciate all the help- Tell me though why the cell that I copied the formula you gave =TEXT(B1,"00\:00")-TEXT(A1,"00\:00") produces the C1 and C2 area as a grey shaded area with the answer ? Mike "Peo Sjoblom" wrote: One way, =TEXT(B1,"00\:00")-TEXT(A1,"00\:00") format as [hh]:mm will return 00:45 if you want minutes as integers multiply the result with 1440 and format as General -- Regards, Peo Sjoblom (No private emails please) "MikeR-Oz" wrote in message ... Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30 at all and just be able to type 1330 as I knw I mean it as 24 hour time -SO LONG AS - the minutes calculated in the third cell is the difference between the first two times. so again I want to type 1330 in A1 and 1415 in B1 and have the C1 calculate the difference as minutes? How may I do this? Happy New Year Mike "Stefi" wrote: „MikeR-Oz” ezt *rta: I have formatted 2 cells say A1 and B1 as time and chose the first type being in the form 13:30 - This shows up in the equation line as 1:12:00 PM. So I type in 13:12 and I get what is required BUT is there a way that I can type 1312 without having to put the ':' in? and still get it to produce the time equation format for my 3 rd cell C1 which will be to calculate the minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the 3rd cell as 0:08 . I have used =+B1-A1 for the C1 cell. Use an extra input column (say Z) and format it as text, and apply this formula in your time column A and B: =TIMEVALUE(LEFT(Z1,2)&":"&RIGHT(Z1,2)) If you enter "1312" in Z1 then A1 will show 13:12 as a real XL time value. Also the other thing is that when I wish to calculate the minutes between the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to have the minutes i.e 60. How can I format this or do I need to write a script/ macro of some sort? Use custom format [mm] Regards, Stefi |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculating elapsed time | andoh | Worksheet Functions | 5 | November 17th, 2005 11:31 AM |
Calculating Time | jeannette_rivera | Using Forms | 6 | August 17th, 2005 10:00 PM |
Imported Date & Time format with calcs. managed in excel from imrp | Todd F. | Worksheet Functions | 0 | July 8th, 2005 09:03 PM |
Calculating (Date and Time) differences | Frank Kabel | Worksheet Functions | 2 | April 27th, 2004 11:19 PM |
Calculating (Date and Time) differences | Madcap | Worksheet Functions | 0 | April 27th, 2004 08:56 AM |