software

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

?ikeR-Oz??ezt ?rta:

gt; I have formatted 2 cells say A1 and B1 as time and chose the first type
gt; being in the form 13:30 - This shows up in the equation line as 1:12:00 PM.
gt; So I type in 13:12 and I get what is required BUT is there a way that I can
gt; type 1312 without having to put the ':' in? and still get it to produce the
gt; time equation format for my 3 rd cell C1 which will be to calculate the
gt; minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the
gt; 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)amp;quot;:quot;amp;RIGHT(Z1,2))

If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time value.gt; Also the other thing is that when I wish to calculate the minutes between
gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to have
gt; the minutes i.e 60. How can I format this or do I need to write a script/
gt; macro of some sort?
gt;Use custom format [mm]

Regards,
StefiCheers 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

quot;Stefiquot; wrote:

gt;
gt;
gt; ?ikeR-Oz??ezt ?rta:
gt;
gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first type
gt; gt; being in the form 13:30 - This shows up in the equation line as 1:12:00 PM.
gt; gt; So I type in 13:12 and I get what is required BUT is there a way that I can
gt; gt; type 1312 without having to put the ':' in? and still get it to produce the
gt; gt; time equation format for my 3 rd cell C1 which will be to calculate the
gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the
gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt;
gt; Use an extra input column (say Z) and format it as text, and apply this
gt; formula in your time column A and B:
gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt;
gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time value.
gt;
gt;
gt; gt; Also the other thing is that when I wish to calculate the minutes between
gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to have
gt; gt; the minutes i.e 60. How can I format this or do I need to write a script/
gt; gt; macro of some sort?
gt; gt;
gt;
gt;
gt; Use custom format [mm]
gt;
gt; Regards,
gt; Stefi
gt;

One way,

=TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)

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)quot;MikeR-Ozquot; gt; wrote in message
...
gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30 at
gt; all and just be able to type 1330 as I knw I mean it as 24 hour time -SO
gt; LONG
gt; AS - the minutes calculated in the third cell is the difference between
gt; the
gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt; have
gt; the C1 calculate the difference as minutes?
gt;
gt; How may I do this?
gt;
gt; Happy New Year
gt; Mike
gt;
gt; quot;Stefiquot; wrote:
gt;
gt;gt;
gt;gt;
gt;gt; ?ikeR-Oz??ezt ?rta:
gt;gt;
gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt;gt; gt; type
gt;gt; gt; being in the form 13:30 - This shows up in the equation line as 1:12:00
gt;gt; gt; PM.
gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way that I
gt;gt; gt; can
gt;gt; gt; type 1312 without having to put the ':' in? and still get it to produce
gt;gt; gt; the
gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate the
gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20 giving
gt;gt; gt; the
gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt;gt;
gt;gt; Use an extra input column (say Z) and format it as text, and apply this
gt;gt; formula in your time column A and B:
gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt;gt;
gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt;gt; value.
gt;gt;
gt;gt;
gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt;gt; gt; between
gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to
gt;gt; gt; have
gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt;gt; gt; script/
gt;gt; gt; macro of some sort?
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt; Use custom format [mm]
gt;gt;
gt;gt; Regards,
gt;gt; Stefi
gt;gt;Thats brilliant Peo- I appreciate all the help- Tell me though why the cell
that I copied the formula you gave =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
produces the C1 and C2 area as a grey shaded area with the answer ?
Mikequot;Peo Sjoblomquot; wrote:

gt; One way,
gt;
gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt;
gt; format as [hh]:mm will return 00:45
gt;
gt; if you want minutes as integers multiply the result with 1440 and format as
gt; General
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;MikeR-Ozquot; gt; wrote in message
gt; ...
gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30 at
gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour time -SO
gt; gt; LONG
gt; gt; AS - the minutes calculated in the third cell is the difference between
gt; gt; the
gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt; gt; have
gt; gt; the C1 calculate the difference as minutes?
gt; gt;
gt; gt; How may I do this?
gt; gt;
gt; gt; Happy New Year
gt; gt; Mike
gt; gt;
gt; gt; quot;Stefiquot; wrote:
gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt; gt;gt;
gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt; gt;gt; gt; type
gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as 1:12:00
gt; gt;gt; gt; PM.
gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way that I
gt; gt;gt; gt; can
gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to produce
gt; gt;gt; gt; the
gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate the
gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20 giving
gt; gt;gt; gt; the
gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt; gt;gt;
gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply this
gt; gt;gt; formula in your time column A and B:
gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt; gt;gt;
gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt; gt;gt; value.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt; gt;gt; gt; between
gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to
gt; gt;gt; gt; have
gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt; gt;gt; gt; script/
gt; gt;gt; gt; macro of some sort?
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Use custom format [mm]
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt; Stefi
gt; gt;gt;
gt;
gt;

And could I * the cell c1 by 1440 ? as in
=TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;)*1440
I have tried and I know this is not correct - what is missing?
Mike

quot;Peo Sjoblomquot; wrote:

gt; One way,
gt;
gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt;
gt; format as [hh]:mm will return 00:45
gt;
gt; if you want minutes as integers multiply the result with 1440 and format as
gt; General
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;MikeR-Ozquot; gt; wrote in message
gt; ...
gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30 at
gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour time -SO
gt; gt; LONG
gt; gt; AS - the minutes calculated in the third cell is the difference between
gt; gt; the
gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt; gt; have
gt; gt; the C1 calculate the difference as minutes?
gt; gt;
gt; gt; How may I do this?
gt; gt;
gt; gt; Happy New Year
gt; gt; Mike
gt; gt;
gt; gt; quot;Stefiquot; wrote:
gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt; gt;gt;
gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt; gt;gt; gt; type
gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as 1:12:00
gt; gt;gt; gt; PM.
gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way that I
gt; gt;gt; gt; can
gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to produce
gt; gt;gt; gt; the
gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate the
gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20 giving
gt; gt;gt; gt; the
gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt; gt;gt;
gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply this
gt; gt;gt; formula in your time column A and B:
gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt; gt;gt;
gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt; gt;gt; value.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt; gt;gt; gt; between
gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need to
gt; gt;gt; gt; have
gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt; gt;gt; gt; script/
gt; gt;gt; gt; macro of some sort?
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Use custom format [mm]
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt; Stefi
gt; gt;gt;
gt;
gt;

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)quot;MikeR-Ozquot; gt; wrote in message
...
gt; Thats brilliant Peo- I appreciate all the help- Tell me though why the
gt; cell
gt; that I copied the formula you gave =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt; produces the C1 and C2 area as a grey shaded area with the answer ?
gt; Mike
gt;
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; One way,
gt;gt;
gt;gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt;gt;
gt;gt; format as [hh]:mm will return 00:45
gt;gt;
gt;gt; if you want minutes as integers multiply the result with 1440 and format
gt;gt; as
gt;gt; General
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; (No private emails please)
gt;gt;
gt;gt;
gt;gt; quot;MikeR-Ozquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30
gt;gt; gt; at
gt;gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour
gt;gt; gt; time -SO
gt;gt; gt; LONG
gt;gt; gt; AS - the minutes calculated in the third cell is the difference between
gt;gt; gt; the
gt;gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt;gt; gt; have
gt;gt; gt; the C1 calculate the difference as minutes?
gt;gt; gt;
gt;gt; gt; How may I do this?
gt;gt; gt;
gt;gt; gt; Happy New Year
gt;gt; gt; Mike
gt;gt; gt;
gt;gt; gt; quot;Stefiquot; wrote:
gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt;gt; gt;gt; gt; type
gt;gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as
gt;gt; gt;gt; gt; 1:12:00
gt;gt; gt;gt; gt; PM.
gt;gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way
gt;gt; gt;gt; gt; that I
gt;gt; gt;gt; gt; can
gt;gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to
gt;gt; gt;gt; gt; produce
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20
gt;gt; gt;gt; gt; giving
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt;gt; gt;gt;
gt;gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply
gt;gt; gt;gt; this
gt;gt; gt;gt; formula in your time column A and B:
gt;gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt;gt; gt;gt;
gt;gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt;gt; gt;gt; value.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt;gt; gt;gt; gt; between
gt;gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need
gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt; have
gt;gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt;gt; gt;gt; gt; script/
gt;gt; gt;gt; gt; macro of some sort?
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Use custom format [mm]
gt;gt; gt;gt;
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Stefi
gt;gt; gt;gt;
gt;gt;
gt;gt;You need to enclose the whole formula in parentheses like in

=(TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;))*1440

after that it is important to format as general or number, NOT time

--
Regards,

Peo Sjoblom

(No private emails please)quot;MikeR-Ozquot; gt; wrote in message
...
gt; And could I * the cell c1 by 1440 ? as in
gt; =TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;)*1440
gt; I have tried and I know this is not correct - what is missing?
gt; Mike
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; One way,
gt;gt;
gt;gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt;gt;
gt;gt; format as [hh]:mm will return 00:45
gt;gt;
gt;gt; if you want minutes as integers multiply the result with 1440 and format
gt;gt; as
gt;gt; General
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; (No private emails please)
gt;gt;
gt;gt;
gt;gt; quot;MikeR-Ozquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30
gt;gt; gt; at
gt;gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour
gt;gt; gt; time -SO
gt;gt; gt; LONG
gt;gt; gt; AS - the minutes calculated in the third cell is the difference between
gt;gt; gt; the
gt;gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt;gt; gt; have
gt;gt; gt; the C1 calculate the difference as minutes?
gt;gt; gt;
gt;gt; gt; How may I do this?
gt;gt; gt;
gt;gt; gt; Happy New Year
gt;gt; gt; Mike
gt;gt; gt;
gt;gt; gt; quot;Stefiquot; wrote:
gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt;gt; gt;gt; gt; type
gt;gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as
gt;gt; gt;gt; gt; 1:12:00
gt;gt; gt;gt; gt; PM.
gt;gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way
gt;gt; gt;gt; gt; that I
gt;gt; gt;gt; gt; can
gt;gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to
gt;gt; gt;gt; gt; produce
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20
gt;gt; gt;gt; gt; giving
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt;gt; gt;gt;
gt;gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply
gt;gt; gt;gt; this
gt;gt; gt;gt; formula in your time column A and B:
gt;gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt;gt; gt;gt;
gt;gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt;gt; gt;gt; value.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt;gt; gt;gt; gt; between
gt;gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need
gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt; have
gt;gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt;gt; gt;gt; gt; script/
gt;gt; gt;gt; gt; macro of some sort?
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; Use custom format [mm]
gt;gt; gt;gt;
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Stefi
gt;gt; gt;gt;
gt;gt;
gt;gt;Cheers Peo- That has solved it for me - fatastic. Have a great New Year.
Mike

quot;Peo Sjoblomquot; wrote:

gt; You need to enclose the whole formula in parentheses like in
gt;
gt; =(TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;))*1440
gt;
gt; after that it is important to format as general or number, NOT time
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;MikeR-Ozquot; gt; wrote in message
gt; ...
gt; gt; And could I * the cell c1 by 1440 ? as in
gt; gt; =TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;)*1440
gt; gt; I have tried and I know this is not correct - what is missing?
gt; gt; Mike
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; One way,
gt; gt;gt;
gt; gt;gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt; gt;gt;
gt; gt;gt; format as [hh]:mm will return 00:45
gt; gt;gt;
gt; gt;gt; if you want minutes as integers multiply the result with 1440 and format
gt; gt;gt; as
gt; gt;gt; General
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; (No private emails please)
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;MikeR-Ozquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as 13:30
gt; gt;gt; gt; at
gt; gt;gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour
gt; gt;gt; gt; time -SO
gt; gt;gt; gt; LONG
gt; gt;gt; gt; AS - the minutes calculated in the third cell is the difference between
gt; gt;gt; gt; the
gt; gt;gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1 and
gt; gt;gt; gt; have
gt; gt;gt; gt; the C1 calculate the difference as minutes?
gt; gt;gt; gt;
gt; gt;gt; gt; How may I do this?
gt; gt;gt; gt;
gt; gt;gt; gt; Happy New Year
gt; gt;gt; gt; Mike
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Stefiquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the first
gt; gt;gt; gt;gt; gt; type
gt; gt;gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as
gt; gt;gt; gt;gt; gt; 1:12:00
gt; gt;gt; gt;gt; gt; PM.
gt; gt;gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way
gt; gt;gt; gt;gt; gt; that I
gt; gt;gt; gt;gt; gt; can
gt; gt;gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to
gt; gt;gt; gt;gt; gt; produce
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to calculate
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20
gt; gt;gt; gt;gt; gt; giving
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply
gt; gt;gt; gt;gt; this
gt; gt;gt; gt;gt; formula in your time column A and B:
gt; gt;gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt; gt;gt; gt;gt; value.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt; gt;gt; gt;gt; gt; between
gt; gt;gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I need
gt; gt;gt; gt;gt; gt; to
gt; gt;gt; gt;gt; gt; have
gt; gt;gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt; gt;gt; gt;gt; gt; script/
gt; gt;gt; gt;gt; gt; macro of some sort?
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Use custom format [mm]
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Regards,
gt; gt;gt; gt;gt; Stefi
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;

Thanks for the feedback
I assume you already had your celebration

--
Regards,

Peo Sjoblom

(No private emails please)quot;MikeR-Ozquot; gt; wrote in message
...
gt; Cheers Peo- That has solved it for me - fatastic. Have a great New Year.
gt; Mike
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; You need to enclose the whole formula in parentheses like in
gt;gt;
gt;gt; =(TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;))*1440
gt;gt;
gt;gt; after that it is important to format as general or number, NOT time
gt;gt;
gt;gt; --
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; (No private emails please)
gt;gt;
gt;gt;
gt;gt; quot;MikeR-Ozquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; And could I * the cell c1 by 1440 ? as in
gt;gt; gt; =TEXT(P8,quot;00\:00quot;)-TEXT(O8,quot;00\:00quot;)*1440
gt;gt; gt; I have tried and I know this is not correct - what is missing?
gt;gt; gt; Mike
gt;gt; gt;
gt;gt; gt; quot;Peo Sjoblomquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; One way,
gt;gt; gt;gt;
gt;gt; gt;gt; =TEXT(B1,quot;00\:00quot;)-TEXT(A1,quot;00\:00quot;)
gt;gt; gt;gt;
gt;gt; gt;gt; format as [hh]:mm will return 00:45
gt;gt; gt;gt;
gt;gt; gt;gt; if you want minutes as integers multiply the result with 1440 and
gt;gt; gt;gt; format
gt;gt; gt;gt; as
gt;gt; gt;gt; General
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Regards,
gt;gt; gt;gt;
gt;gt; gt;gt; Peo Sjoblom
gt;gt; gt;gt;
gt;gt; gt;gt; (No private emails please)
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;MikeR-Ozquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Cheers Stefi- Tell me though - I am happy not to have the cell as
gt;gt; gt;gt; gt; 13:30
gt;gt; gt;gt; gt; at
gt;gt; gt;gt; gt; all and just be able to type 1330 as I knw I mean it as 24 hour
gt;gt; gt;gt; gt; time -SO
gt;gt; gt;gt; gt; LONG
gt;gt; gt;gt; gt; AS - the minutes calculated in the third cell is the difference
gt;gt; gt;gt; gt; between
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; first two times. so again I want to type 1330 in A1 and 1415 in B1
gt;gt; gt;gt; gt; and
gt;gt; gt;gt; gt; have
gt;gt; gt;gt; gt; the C1 calculate the difference as minutes?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; How may I do this?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Happy New Year
gt;gt; gt;gt; gt; Mike
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Stefiquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; ?ikeR-Oz??ezt ?rta:
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; gt; I have formatted 2 cells say A1 and B1 as time and chose the
gt;gt; gt;gt; gt;gt; gt; first
gt;gt; gt;gt; gt;gt; gt; type
gt;gt; gt;gt; gt;gt; gt; being in the form 13:30 - This shows up in the equation line as
gt;gt; gt;gt; gt;gt; gt; 1:12:00
gt;gt; gt;gt; gt;gt; gt; PM.
gt;gt; gt;gt; gt;gt; gt; So I type in 13:12 and I get what is required BUT is there a way
gt;gt; gt;gt; gt;gt; gt; that I
gt;gt; gt;gt; gt;gt; gt; can
gt;gt; gt;gt; gt;gt; gt; type 1312 without having to put the ':' in? and still get it to
gt;gt; gt;gt; gt;gt; gt; produce
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; time equation format for my 3 rd cell C1 which will be to
gt;gt; gt;gt; gt;gt; gt; calculate
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; minutes difference between the 2 first cells eg. 13:12 and 13:20
gt;gt; gt;gt; gt;gt; gt; giving
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; 3rd cell as 0:08 . I have used = B1-A1 for the C1 cell.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Use an extra input column (say Z) and format it as text, and apply
gt;gt; gt;gt; gt;gt; this
gt;gt; gt;gt; gt;gt; formula in your time column A and B:
gt;gt; gt;gt; gt;gt; =TIMEVALUE(LEFT(Z1,2)amp;quot;:quot;amp;RIGHT(Z1,2))
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; If you enter quot;1312quot; in Z1 then A1 will show 13:12 as a real XL time
gt;gt; gt;gt; gt;gt; value.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; gt; Also the other thing is that when I wish to calculate the minutes
gt;gt; gt;gt; gt;gt; gt; between
gt;gt; gt;gt; gt;gt; gt; the 2 times eg 13:00 and 14:00, I get 1:00 as the answer but I
gt;gt; gt;gt; gt;gt; gt; need
gt;gt; gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt;gt; gt; have
gt;gt; gt;gt; gt;gt; gt; the minutes i.e 60. How can I format this or do I need to write a
gt;gt; gt;gt; gt;gt; gt; script/
gt;gt; gt;gt; gt;gt; gt; macro of some sort?
gt;gt; gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Use custom format [mm]
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Regards,
gt;gt; gt;gt; gt;gt; Stefi
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;