I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

Hello Debbie,

B47:K47 are 10 cells, not 5. Which cells are containing your data and
how are they formatted (integer numbers, date / time format, ...)?

Can you show some sample data?

Regards,
Bernd

Debbie

Show us the formula that calculates the times. One suggestion to begin with
is format the cell with this formula in as [h]:mm

Mike

"Debbie" wrote:

I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

On Oct 11, 9:47*am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))

On Oct 11, 10:01*am, Bernd P wrote:
Hello Debbie,

B47:K47 are 10 cells, not 5. Which cells are containing your data and
how are they formatted (integer numbers, date / time format, ...)?

Can you show some sample data?

Regards,
Bernd

I have a formula in cell B47, then the B48 is blank and so on.

This is the formula in the 5 cells.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))

On Oct 11, 10:07*am, Debbie wrote:
On Oct 11, 10:01*am, Bernd P wrote:

Hello Debbie,

B47:K47 are 10 cells, not 5. Which cells are containing your data and
how are they formatted (integer numbers, date / time format, ...)?

Can you show some sample data?

Regards,
Bernd

I have a formula in cell B47, then the B48 is blank and so on.

This is the formula in the 5 cells.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))

The cells that have the formula in it are as a Text. The result that
is in the cell is 2:00 for example.

Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0 ),IF(H46="A",TIME(2,0,0),I46-H46)))

Format this and the sum cell as [h]:mm

Mike

"Debbie" wrote:

On Oct 11, 9:47 am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))

Hello Debbie,

Use
=SUM(--B46:F46)
and format as [h]:mm

Regards,
Bernd

Hi Debbie,

Try with timevalue() instead of string formatted time:

"02:00" = timevalue("02:00")

I see a second issue, you try to 'sum' strings with I46-H46.

Wkr,

JP



"Debbie" wrote in message
...
On Oct 11, 9:47 am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))

On Oct 11, 10:20*am, Mike H wrote:
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0 ),IF(H46="A",TIME(2,0,0),*I46-H46)))

Format this and the sum cell as [h]:mm

Mike



"Debbie" wrote:
On Oct 11, 9:47 am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -

- Show quoted text -

The sign in and out times are formated as time h:mm AM/PM
H46: 3:00 PM I46: 5:10 PM

Then the formula I have to give the difference between the 2 times to
get the total hours attended is:
=IF(H46="s","2.00",IF(H46="CLOSED","0.00",IF(H46=" A","2.00",TEXT(I46-
H46,"h:mm"))))
My answer is Total Hours: 2.10 (2 hours, 10 min)

Then my third formula must sum the total hours attended.



The formula you gave me does not work. It just shows the formula in
cell.

On Oct 11, 10:20*am, Mike H wrote:
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0 ),IF(H46="A",TIME(2,0,0),*I46-H46)))

Format this and the sum cell as [h]:mm

Mike



"Debbie" wrote:
On Oct 11, 9:47 am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -

- Show quoted text -

Thank you! This worked. You guys are great!

On Oct 11, 10:20*am, Mike H wrote:
Debbie,

your trying to sum text values, try this

=IF(H46="s",TIME(2,0,0),IF(H46="CLOSED",TIME(0,0,0 ),IF(H46="A",TIME(2,0,0),*I46-H46)))

Format this and the sum cell as [h]:mm

Mike



"Debbie" wrote:
On Oct 11, 9:47 am, Debbie wrote:
I have formulas in 5 columns that give me the total hours a child is
in daycare. The formula calulates the total time according to there
sign in and out times. That works great. But when I sum those
formulas, it says "0". What am I doing wrong?

=SUM(B47:K47)

This is the formula I have that calculates the time in attendance if
this helps. Those results are what I am trying to sum.

=IF(H46="s","2:00",IF(H46="CLOSED","0:00",IF(H46=" A","2:00",TEXT(I46-
H46,"h:mm"))))- Hide quoted text -

- Show quoted text -

One more question. My sum column, How do i round the answer to the
nearest 1/4 hour?

=SUM(--B46:F46)

That would have to be array entered.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello Debbie,

Use
=SUM(--B46:F46)
and format as [h]:mm

Regards,
Bernd

On Oct 11, 12:41*pm, "T. Valko" wrote:
=SUM(--B46:F46)

That would have to be array entered.

--
Biff
Microsoft Excel MVP

"Bernd P" wrote in message

...



Hello Debbie,

Use
=SUM(--B46:F46)
and format as [h]:mm

Regards,
Bernd- Hide quoted text -

- Show quoted text -

Biff, How do I enter an array? Also, how would I make this formula
round to the nearest 1/4 of an hour?

Hello,

Thanks, Biff, you are right.

=ROUND(SUM(--B46:F46)*24*4,0)/24/4
array-entered gives the sum rounded to a quarter of an hour.
To array-enter enter with CTRL + SHIFT + ENTER, not only ENTER. Curly
brackets will appear around your formula...

Regards,
Bernd

On Oct 11, 2:03*pm, Bernd P wrote:
Hello,

Thanks, Biff, you are right.

=ROUND(SUM(--B46:F46)*24*4,0)/24/4
array-entered gives the sum rounded to a quarter of an hour.
To array-enter enter with CTRL + SHIFT + ENTER, not only ENTER. Curly
brackets will appear around your formula...

Regards,
Bernd

Thank you! All formulas are working great. I love all you do for all
of us. You help to make our lives so much easier. I am learning more
and more about formulas. Soon, excel will be doing all my work. I will
only have to enter referring data. How awesome is that. Again, thank
you!