Cells with time format and calculating the diffrence

[Guest]

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

 

[Guest]

„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

 

[Guest]

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:

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

 

[Peo Sjoblom]

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)

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:

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

 

[Guest]

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,

=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)

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:

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

 

[Guest]

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,

=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)

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:

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

 

[Peo Sjoblom]

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)

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,

=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)

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:

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

 

[Peo Sjoblom]

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)

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,

=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)

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:

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

 

[Guest]

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

=(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)

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,

=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)


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:

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

 

[Peo Sjoblom]

Thanks for the feedback
I assume you already had your celebration

--
Regards,

Peo Sjoblom

(No private emails please)

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

=(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)

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,

=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)


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:

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

 

[Guest]

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

--
Regards,

Peo Sjoblom

(No private emails please)

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

=(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)


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,

=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)


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:

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

 

[Guest]

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

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)

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:

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