Display minutes ONLY and calc difference

G

Guest

In creating a soccer scorecard I would like to figure out how to display on
the scorecard minutes played during a game. Our soccer team plays four 9
minute quarters (36 minutes total). I want to display for each player the
time IN and OUT and then TOTAL minutes played. But, I don't want Excel to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7 minutes in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub out at
22 minutes. He started the 3rd qtr at 18 minutes and played the entire qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I want it to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00. Is
this possible?
 
P

Peo Sjoblom

Why don't you just use integers and subtract, start in A1, subbed in B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)
 
G

Guest

I formatted the cell (I used the Custom mm:ss, but I changed it to show your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in the
formula bar a date plus time that doesn't make sense. And, in the cell it
shows a huge number, like 51840:00. How am I, exactly, to enter the minutes
of, say, 36 or 27 or 9, etc?

Peo Sjoblom said:
Why don't you just use integers and subtract, start in A1, subbed in B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
In creating a soccer scorecard I would like to figure out how to display
on
the scorecard minutes played during a game. Our soccer team plays four 9
minute quarters (36 minutes total). I want to display for each player the
time IN and OUT and then TOTAL minutes played. But, I don't want Excel to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7 minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I want it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00. Is
this possible?
 
P

Peo Sjoblom

If you use time format e.g the custom format I showed then enter a time like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan 0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
I formatted the cell (I used the Custom mm:ss, but I changed it to show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in the
formula bar a date plus time that doesn't make sense. And, in the cell it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

Peo Sjoblom said:
Why don't you just use integers and subtract, start in A1, subbed in B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team plays four
9
minute quarters (36 minutes total). I want to display for each player
the
time IN and OUT and then TOTAL minutes played. But, I don't want Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I want
it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00.
Is
this possible?
 
G

Guest

Sorry, I was dense. I re-read your message and realized I had to type in the
colon and all.

Now, my last issue...I'm trying to show a '% of game played.' For example,
36 min is the total game time. After using the custom formula and doing a
formula to show the TOTAL minutes played, how do I show what % of 36 minutes
(i.e. total game time) they played??

Peo Sjoblom said:
If you use time format e.g the custom format I showed then enter a time like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan 0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
I formatted the cell (I used the Custom mm:ss, but I changed it to show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in the
formula bar a date plus time that doesn't make sense. And, in the cell it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

Peo Sjoblom said:
Why don't you just use integers and subtract, start in A1, subbed in B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team plays four
9
minute quarters (36 minutes total). I want to display for each player
the
time IN and OUT and then TOTAL minutes played. But, I don't want Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I want
it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00.
Is
this possible?
 
G

Guest

In addition to my previous question I have to, somehow, convert the minutes
into integers so that I can perform percentage, correct? If so, how do I do
that?

Peo Sjoblom said:
If you use time format e.g the custom format I showed then enter a time like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan 0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
I formatted the cell (I used the Custom mm:ss, but I changed it to show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in the
formula bar a date plus time that doesn't make sense. And, in the cell it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

Peo Sjoblom said:
Why don't you just use integers and subtract, start in A1, subbed in B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team plays four
9
minute quarters (36 minutes total). I want to display for each player
the
time IN and OUT and then TOTAL minutes played. But, I don't want Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I want
it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00.
Is
this possible?
 
P

Peo Sjoblom

This is an answer to both your latest question, you can certainly use
percentage when calculating time assume
C2 holds the time 0:36:00 and D2 holds the total time played, for instance
00:21:00 displayed as 21:00

=D2/C2

format result as percentage returns 58.33%

if you want to convert minutes to integers multiply with 1440 and format as
general or number

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
Sorry, I was dense. I re-read your message and realized I had to type in
the
colon and all.

Now, my last issue...I'm trying to show a '% of game played.' For
example,
36 min is the total game time. After using the custom formula and doing a
formula to show the TOTAL minutes played, how do I show what % of 36
minutes
(i.e. total game time) they played??

Peo Sjoblom said:
If you use time format e.g the custom format I showed then enter a time
like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan 0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
I formatted the cell (I used the Custom mm:ss, but I changed it to show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in
the
formula bar a date plus time that doesn't make sense. And, in the cell
it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

:

Why don't you just use integers and subtract, start in A1, subbed in
B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team plays
four
9
minute quarters (36 minutes total). I want to display for each
player
the
time IN and OUT and then TOTAL minutes played. But, I don't want
Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the
entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I
want
it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00.
Is
this possible?
 
G

Guest

Great, it works. But, I want to have in the CELL the formula D2/00:36:00 and
it won't take it. Do I have to have a C2 cell that show 36 minutes of
playing time that the formula will pull from?

Peo Sjoblom said:
This is an answer to both your latest question, you can certainly use
percentage when calculating time assume
C2 holds the time 0:36:00 and D2 holds the total time played, for instance
00:21:00 displayed as 21:00

=D2/C2

format result as percentage returns 58.33%

if you want to convert minutes to integers multiply with 1440 and format as
general or number

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
Sorry, I was dense. I re-read your message and realized I had to type in
the
colon and all.

Now, my last issue...I'm trying to show a '% of game played.' For
example,
36 min is the total game time. After using the custom formula and doing a
formula to show the TOTAL minutes played, how do I show what % of 36
minutes
(i.e. total game time) they played??

Peo Sjoblom said:
If you use time format e.g the custom format I showed then enter a time
like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan 0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


I formatted the cell (I used the Custom mm:ss, but I changed it to show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows in
the
formula bar a date plus time that doesn't make sense. And, in the cell
it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

:

Why don't you just use integers and subtract, start in A1, subbed in
B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team plays
four
9
minute quarters (36 minutes total). I want to display for each
player
the
time IN and OUT and then TOTAL minutes played. But, I don't want
Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the 1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the
entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I
want
it
to
show all the MINUTES and then grand total the TOTAL minutes played.

I can't figure out how to format the cell and then how to input the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00, 02:00.
Is
this possible?
 
P

Peo Sjoblom

Try

=D2/"00:36:00"


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
Great, it works. But, I want to have in the CELL the formula D2/00:36:00
and
it won't take it. Do I have to have a C2 cell that show 36 minutes of
playing time that the formula will pull from?

Peo Sjoblom said:
This is an answer to both your latest question, you can certainly use
percentage when calculating time assume
C2 holds the time 0:36:00 and D2 holds the total time played, for
instance
00:21:00 displayed as 21:00

=D2/C2

format result as percentage returns 58.33%

if you want to convert minutes to integers multiply with 1440 and format
as
general or number

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


tjsmags said:
Sorry, I was dense. I re-read your message and realized I had to type
in
the
colon and all.

Now, my last issue...I'm trying to show a '% of game played.' For
example,
36 min is the total game time. After using the custom formula and
doing a
formula to show the TOTAL minutes played, how do I show what % of 36
minutes
(i.e. total game time) they played??

:

If you use time format e.g the custom format I showed then enter a
time
like
0:36:00
36 and using time format means that excel sees it as 36 days after Jan
0
1900 or 36x24x60 minutes
Use 0:36:00, if you use 36 use General format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


I formatted the cell (I used the Custom mm:ss, but I changed it to
show
your
formula of [mm]:ss). But, when I enter a number, like 36, it shows
in
the
formula bar a date plus time that doesn't make sense. And, in the
cell
it
shows a huge number, like 51840:00. How am I, exactly, to enter the
minutes
of, say, 36 or 27 or 9, etc?

:

Why don't you just use integers and subtract, start in A1, subbed
in
B1

=A1-B1

Or use the same formula, custom format as [mm]:ss

then enter the times as 0:36:00 and 0:27:00

Then total and =SUM(Range) and use the same custom format

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


In creating a soccer scorecard I would like to figure out how to
display
on
the scorecard minutes played during a game. Our soccer team
plays
four
9
minute quarters (36 minutes total). I want to display for each
player
the
time IN and OUT and then TOTAL minutes played. But, I don't want
Excel
to
convert this into ACTUAL time (as in time of day).

For example, I want the spreadsheet to show Player A started the
1st
quarter
at 36 minutes. He substituted out at 29 minutes (so he played 7
minutes
in
the 1st quarter). He started the 2nd qtr at 27 minutes and then
sub
out
at
22 minutes. He started the 3rd qtr at 18 minutes and played the
entire
qtr.
He started the last qtr at 9 minutes and sub out at 2 minutes. I
want
it
to
show all the MINUTES and then grand total the TOTAL minutes
played.

I can't figure out how to format the cell and then how to input
the
minutes.
I would like to see 36:00, 29:00, 27:00, 22:00, 18:00, 09:00,
02:00.
Is
this possible?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top