Adding hours and minutes

[Patrick]

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Bob Phillips]

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Patrick]

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[tjtjjtjt]

Putting the braces around the hours will make the time go passed 23:59. So,
change your format to:
[hh]:mm

tj

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Ron Rosenfeld]

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

Just format the total cell as [hh]:mm

Patrick,

Reread Bob's answer and note the brackets around the h in the format he
recommended.

The behavior you describe is seen when you do NOT format it as Bob recommended.

In other words:

h:mm rolls over at 24
[h]:mm does NOT roll over at 24


--ron

 

[Patrick]

Hey! Just like Bob said! Thank you both so much! The brackets made all the
difference. I've been working on this for days. So glad to have found you.
Thanks again Bob and "tjtjjtjt!"

Putting the braces around the hours will make the time go passed 23:59. So,
change your format to:
[hh]:mm

tj

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all
over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to
just
keep increasing in the 15 minute increments I need to use, changing the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Patrick]

Thanks Ron,

I already wrote back after it was pointed out that the brackets had to be
inserted. Works great now!

Patrick

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

Just format the total cell as [hh]:mm

Patrick,

Reread Bob's answer and note the brackets around the h in the format he
recommended.

The behavior you describe is seen when you do NOT format it as Bob recommended.

In other words:

h:mm rolls over at 24
[h]:mm does NOT roll over at 24


--ron

 

[tjtjjtjt]

Happy to help. Thank you for the feedback.

tj

Hey! Just like Bob said! Thank you both so much! The brackets made all the
difference. I've been working on this for days. So glad to have found you.
Thanks again Bob and "tjtjjtjt!"

Putting the braces around the hours will make the time go passed 23:59. So,
change your format to:
[hh]:mm

tj

Bob, the format is fine. It's just that when you get to 24:00 it switches
back to 0:00. I need it to keep adding all the way up to 70:00.

Patrick

:

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all
over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to
just
keep increasing in the 15 minute increments I need to use, changing the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Sharmila]

I have to calculate the hours for my employees per week and i wanted to get
some help on this particular subject.
can u show me step by step how to calculate the total amount of hours worked
and the total hours for the week?
i am quite new to the hour formula and i need some assistance , is there
anyone who can help me?
pls use the following eg. in your explanation
John Smith
time in - 9am
time out- 12:30pm* (*this is lunch break)
time in -1pm
time out -4:30 pm
best regards
sharmila

 

[Peo Sjoblom]

=A5-A2-(A4-A3)

meaning

End-Start-(LunchIn-LunchOut)

format result as hh:mm

 

[Steve Walton]

=A5-A2-(A4-A3)

meaning

End-Start-(LunchIn-LunchOut)

format result as hh:mm

or [hh]:mm if you want to see total hours over 24
ie weekly total 38:45


Steve

 

[Maggie]

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM). The
total will be several hundred or even thousands of hours. I have tried every
suggested found out here and the best I get is a 00:00 total. Please show me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[David Biddulph]

Have you done as Bob suggested and formatted the total cell as [hh]:mm ?
If so, what formula are you using, what data values, and what value do you
see if you temporarily change the total call's format to Number rather than
your Custom [hh]:mm ?
If you are seeing zero at that stage, then it looks as if your data cells
probably contain text, rather than real times.
--
David Biddulph

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM). The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Peo Sjoblom]

Use a custom format of [H]:mm, if it is equal or greater than 10,000 you
would need to use
decimal hours


--


Regards,


Peo Sjoblom

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM). The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)

I want to add hours and minutes in Excel but want the result to be displayed
as hours and minutes. not in decimal form. In other words, if I have Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23 hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and to just
keep increasing in the 15 minute increments I need to use, changing the total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Maggie]

Thanks David for getting back to me...
This file was converted to an excel file and yes, Im sure the data was
formatted as Text. If I convert the file again should I set the format to
'General' 'Number'?
As far as the formula it is Example: sum=(I1:I267)*24, as I said the number
of hours could be in the thousands.
The total cell was formatted to [hh]:mm
If this gives you any ideas please be specific and spell it out for this
'wannabe'
thanks
Maggie
--
Guru Wannabe

Have you done as Bob suggested and formatted the total cell as [hh]:mm ?
If so, what formula are you using, what data values, and what value do you
see if you temporarily change the total call's format to Number rather than
your Custom [hh]:mm ?
If you are seeing zero at that stage, then it looks as if your data cells
probably contain text, rather than real times.
--
David Biddulph

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM). The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all
over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and to
just
keep increasing in the 15 minute increments I need to use, changing the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[Maggie]

Hi Peo, I have formatted the total cell as [h]:mm, I tested the formula and
did get a total, its incorrect but at least I got something other than 00:00.
The total of all the hours will be over 10,000, so now comes the next
question how do I convert to decimal in Excel? I know you are answering many
questions and I thank you for taking time with me. I need to see an example
of the formula if you would.
Thanks Maggie
--
Guru Wannabe

Use a custom format of [H]:mm, if it is equal or greater than 10,000 you
would need to use
decimal hours


--


Regards,


Peo Sjoblom

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM). The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15 minute
increments. I run into the expected problem when I get beyond 23:45 (23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all
over
again and gives me 0:00. I do understand that Excel sees these type of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and to
just
keep increasing in the 15 minute increments I need to use, changing the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[David Biddulph]

Are you sure that you wanted to multiply by 24?
That sounds more like a conversion to decimal hours (which you'd format as
Number or General, rather than as time).
--
David Biddulph

Thanks David for getting back to me...
This file was converted to an excel file and yes, Im sure the data was
formatted as Text. If I convert the file again should I set the format to
'General' 'Number'?
As far as the formula it is Example: sum=(I1:I267)*24, as I said the
number
of hours could be in the thousands.
The total cell was formatted to [hh]:mm
If this gives you any ideas please be specific and spell it out for this
'wannabe'
thanks
Maggie
--
Guru Wannabe

Have you done as Bob suggested and formatted the total cell as [hh]:mm ?
If so, what formula are you using, what data values, and what value do
you
see if you temporarily change the total call's format to Number rather
than
your Custom [hh]:mm ?
If you are seeing zero at that stage, then it looks as if your data cells
probably contain text, rather than real times.
--
David Biddulph

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM).
The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please
show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe


:

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I
have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15
minute
increments. I run into the expected problem when I get beyond 23:45
(23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all
over
again and gives me 0:00. I do understand that Excel sees these type
of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and
to
just
keep increasing in the 15 minute increments I need to use, changing
the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!

 

[T. Valko]

A formula can return a time value >10,000 hrs when formatted as [h]:mm

You just can't *manually* enter a time value >9999:99:99

--
Biff
Microsoft Excel MVP

Hi Peo, I have formatted the total cell as [h]:mm, I tested the formula
and
did get a total, its incorrect but at least I got something other than
00:00.
The total of all the hours will be over 10,000, so now comes the next
question how do I convert to decimal in Excel? I know you are answering
many
questions and I thank you for taking time with me. I need to see an
example
of the formula if you would.
Thanks Maggie
--
Guru Wannabe

Use a custom format of [H]:mm, if it is equal or greater than 10,000 you
would need to use
decimal hours


--


Regards,


Peo Sjoblom

Hi Bob, I too am trying to add a column of Hours and minutes (HHH:MM).
The
total will be several hundred or even thousands of hours. I have tried
every
suggested found out here and the best I get is a 00:00 total. Please
show
me
the formula one step at a time. Thank you in Advance... Maggie
--
Guru Wannabe


:

Just format the total cell as [hh]:mm

--

HTH

RP
(remove nothere from the email address if mailing direct)


I want to add hours and minutes in Excel but want the result to be
displayed
as hours and minutes. not in decimal form. In other words, if I
have
Excel
add 17:30 +17:30+17:30+17:30 I want the total to come out to 70:00,
representing 70 hours and 0 minutes. I am increasing time in 15
minute
increments. I run into the expected problem when I get beyond 23:45
(23
hours
and 59) minutes. Then Excel, seeing this as time (mindnight) starts
all
over
again and gives me 0:00. I do understand that Excel sees these type
of
entries (17:30) as time and that is why it switches to 0:00 when it
hits
24:00. I am just trying to find a way to tell it NOT to do that and
to
just
keep increasing in the 15 minute increments I need to use, changing
the
total
hours by 1 whenever the minutes reach :60.

Many thanks in advance for any suggestions!