 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2006Cells with time format and calculating the diffrence
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. 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? Any advise? Mike „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. [quoted text clipped - 3 lines] > 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 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 > „MikeR-Oz” ezt írta: > [quoted text clipped - 21 lines] > Regards, > Stefi 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  SignatureRegards, Peo Sjoblom (No private emails please) > 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 [quoted text clipped - 44 lines] >> Regards, >> Stefi 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 > One way, > [quoted text clipped - 53 lines] > >> Regards, > >> Stefi I have no idea why, the formula certainly won't do it, maybe you have some conditional formatting applied somewhere? SignatureRegards, Peo Sjoblom (No private emails please) > Thats brilliant Peo- I appreciate all the help- Tell me though why the > cell [quoted text clipped - 69 lines] >> >> Regards, >> >> Stefi 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") [quoted text clipped - 58 lines] > > >> Regards, > > >> Stefi 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 > One way, > [quoted text clipped - 53 lines] > >> Regards, > >> Stefi 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 SignatureRegards, Peo Sjoblom (No private emails please) > And could I * the cell c1 by 1440 ? as in > =TEXT(P8,"00\:00")-TEXT(O8,"00\:00")*1440 [quoted text clipped - 68 lines] >> >> Regards, >> >> Stefi Cheers Peo- That has solved it for me - fatastic. Have a great New Year. Mike > You need to enclose the whole formula in parentheses like in > [quoted text clipped - 74 lines] > >> >> Regards, > >> >> Stefi Thanks for the feedback I assume you already had your celebration SignatureRegards, Peo Sjoblom (No private emails please) > Cheers Peo- That has solved it for me - fatastic. Have a great New Year. > Mike [quoted text clipped - 84 lines] >> >> >> Regards, >> >> >> Stefi Oh Yes! Down under is into the 1st Jan ahead of you all at 1540 hours. > Thanks for the feedback > I assume you already had your celebration [quoted text clipped - 87 lines] > >> >> >> Regards, > >> >> >> Stefi |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.