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




0
MikeROz (103)
12/30/2005 6:59:02 AM
excel.newusers 15348 articles. 2 followers. Follow

11 Replies
470 Views

Similar Articles

[PageSpeed] 12


„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

0
Stefi (275)
12/30/2005 11:09:02 AM
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
> 
0
MikeROz (103)
12/31/2005 5:50:01 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> 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
>> 

0
terre081 (3244)
12/31/2005 6:10:56 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> > 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
> >> 
> 
> 
0
MikeROz (103)
12/31/2005 7:04:01 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> > 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
> >> 
> 
> 
0
MikeROz (103)
12/31/2005 7:10:02 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
news:C9CA3E44-F304-4270-9C9C-C40C1B10304E@microsoft.com...
> 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" <MikeROz@discussions.microsoft.com> wrote in message
>> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
>> > 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
>> >>
>>
>> 

0
terre081 (3244)
12/31/2005 6:11:13 PM
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" <MikeROz@discussions.microsoft.com> wrote in message 
news:1829A0FA-F268-4F57-B435-953AE0CB8AD3@microsoft.com...
> 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" <MikeROz@discussions.microsoft.com> wrote in message
>> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
>> > 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
>> >>
>>
>> 

0
terre081 (3244)
12/31/2005 6:14:46 PM
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" <MikeROz@discussions.microsoft.com> wrote in message 
> news:1829A0FA-F268-4F57-B435-953AE0CB8AD3@microsoft.com...
> > 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" <MikeROz@discussions.microsoft.com> wrote in message
> >> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> >> > 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
> >> >>
> >>
> >> 
> 
> 
0
MikeROz (103)
12/31/2005 11:25:02 PM
Thanks for the feedback
I assume you already had your celebration

-- 
Regards,

Peo Sjoblom

(No private emails please)


"MikeR-Oz" <MikeROz@discussions.microsoft.com> wrote in message 
news:566A0DBD-F50E-49D8-A35A-8C957311580D@microsoft.com...
> 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" <MikeROz@discussions.microsoft.com> wrote in message
>> news:1829A0FA-F268-4F57-B435-953AE0CB8AD3@microsoft.com...
>> > 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" <MikeROz@discussions.microsoft.com> wrote in message
>> >> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
>> >> > 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
>> >> >>
>> >>
>> >>
>>
>> 

0
terre081 (3244)
1/1/2006 1:41:32 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
> news:566A0DBD-F50E-49D8-A35A-8C957311580D@microsoft.com...
> > 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" <MikeROz@discussions.microsoft.com> wrote in message
> >> news:1829A0FA-F268-4F57-B435-953AE0CB8AD3@microsoft.com...
> >> > 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" <MikeROz@discussions.microsoft.com> wrote in message
> >> >> news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> >> >> > 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
> >> >> >>
> >> >>
> >> >>
> >>
> >> 
> 
> 
0
MikeROz (103)
1/1/2006 4:42:01 AM
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" <MikeROz@discussions.microsoft.com> wrote in message 
> > news:1935F92E-D040-4D9B-A1E8-C8BD924AB1B9@microsoft.com...
> > > 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
> > >> 
> > 
> > 
0
Stefi (275)
1/3/2006 10:11:02 AM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Paragraph formating jumps to defaults.
Hi, We are using Office/Outlook 2007 We have an email signature script that is run when users login, it´s working ok except for two things; If the default paragraph settings in Word 2007 for a user matches those in the signature script, the signature will use the "Base defaults" of Word 2007, that is SpaceAfter = 10 and LineSpacing = "Multiple" with a value of 1,15. So if I change the script to use SpaceAfter=0 and the user has set his/her Word 2007 to use SpaceAfter=0, the result will be SpaceAfter=10. If the script is set to SpaceAfter=0 and the us...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Time Series
I have about 30000 lines of data that looks like so: Time Count 93000 447 93001 1020 93002 1438 93003 2177 93004 3042 93005 3467 93006 2913 93007 3341 93008 3547 93009 2908 93010 3512 93011 4090 Where ColA is time. I am having trouble creating a graph with time on the x-axis and count on the y-axis. Thank you in advance. You need to make an XY chart But with 30,000 data points you could run into trouble; Help says you can 32,000 data points in a single data series. However, if you want markers it will look like heck. You could limit the data like this: assume y-values in B2:B30001 in ...

Where is the lasso feature in 2008? (was in formatting palette in 2004)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>In Excel 2004 in the formatting palette in image, there are different selection marquees and lasso's. Where are these features in 2008 Excel? I can't find them anywhere. <br><br>thnx I just found my answer, the &quot;genius's&quot; at Microsoft / MAC Office decided to kill off all these editing features... <br><br>Over $200 to upgrade to a product with LESS features... I don't think so... unbelievable, I will continue to use 2004 because 2008...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

formatting auto replies
Does anyone know of a method that allows outlook 2k2 to format automatic replies regardless of the format of the original message? I setup this automatic response rule that replies with a template, but problem is, when replying to http clients (hotmail or yahoo), the message at the receiver's end has words totally misplaced, all over the page. Any suggestions would be appreciated. ...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Forumla to calculate a percentage
I have a column of figures that I need to calculate that if the figure in column A is Transport costs to us is �100 and column B is the price sold to the customer is �25.00, i need the third column to work out < 72% Theoretical Transport Recovery in other words if the value in column B is <72% mark it red Like wise for fuel is the value in column B is <132% mark it red Try as I might I cannot get it to work it out any ideas Julie, This formula gives the correct percentage... =(B1-A1)/A1 Format the formula cell with this custom number format... 0%;[Red]-0% If you don'...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Varying format
Is there any way to format a cell differently depending on the number of decimal places typed in? What I need is a minimum of 2dp but 3 if the typed entry matches. eg 1.2 appears as 1.20 and 1.234 appears as typed. TIA Ian Hi! Try formatting the cell with a custom number format such as 0.00## Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=27494 "AlfD" <AlfD.1f5q6o@excelforum-nospam.c...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display the time increments in 15 minute blocks, but on the Appointments form, the Start and End Times available on the drop-down menus, respectively, are in 30 minute increment blocks only. How can I set the format for the appointment page to have the Start and End Times for appointments have increment time blocks of 15 minutes? Thanks! My direct email address is: solutioncounselor@comcast.net . If you set your appointments in 15 minute blocks Outlook will start to offer that. It takes a few uses to work. -- Nikki Peterson [MVP ...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

difficult format
I want to format a code like this: ##.0001.## so then i push it down and gives me 0002 and so on, the other ## r numbers. The problem is that when i do that, it sums in the last two numbers instead of what i want, so i ask if it is possible to format like this: (text)##.(number)0001.(text)## so it guives me the sum of number then do what i want. if it is possible how ca i do that? Thanks You can try this, starting in row 1: ="##."&TEXT(ROW(),"0000")&".##" You'll have to adjust the "Row()" number when starting in any other row. For example,...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...