| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Cells 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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
€ž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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
I have no idea why, the formula certainly won't do it, maybe you have some
conditional formatting applied somewhere? -- Regards, Peo Sjoblom (No private emails please) "MikeR-Oz" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Cells with time format and calculating the diffrence
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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating elapsed time | Excel Worksheet Functions | |||
| Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions | |||
| Cells formated as numbers are calculating like text | Excel Discussion (Misc queries) | |||
| Calculating negative time & creating scrol bars for fixed size txt box. | Excel Discussion (Misc queries) | |||
| calculating with a time format cell | Excel Worksheet Functions | |||
Linear Mode
