Format problem

[Sapphyre]

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another sheet
within my workbook I calculate different information that I get from my main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01 occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2 column B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[richard]

If you look in the formula bar of any of the cells in Sheet 1 column J you
may see that by formatting the cell to (MM) you have converted the 01 data to
a date which is now represented as 01/01/1900, therefore your formula which
is looking for instances of 01 will find nothing, play with the format of
column J or alter the formula in sheet 2 thro 60 to look for the appropriate
date

 

[bj]

maybe
=sumproduct(--(minute(Sheet1!J2:J2500)=1))



9

 

[Sapphyre]

Thanks for the reply Richard. Ok I looked in my when I click on a cell in J
Sheet1 and it has my formula =H2-G2 and is formatted [mm]. I don't see
anything to make me think it is date formatted. I've tried changing the
format and it doesn't work for that column. I had to format it this way so
that it would calculate the subtraction of the Date/Time in columns H and G
correctly, because sometimes it is over 24 hours and sometimes crosses
midnight.

Anyway, let me show you how I have it set up.
Sheet1:

G H I J
Started Ended Length TTL Min
08/19/07 22:30 8/20/07 07:30 0 days 09:00 540

G: formatted mm/dd/yy hh:mm
H: formatted mm/dd/yy hh:mm
I: formula =H2-G2 formatted d "days" hh:mm
J: formula =H2-G2 formatted [mm]

Sheet2 column B: What I want here is for it to calculate the number of
accurances of specific minutes. I use to have it set up like this and it
worked, until I changed J format to [mm].
=COUNTIF(Sheet1!J2:J2500,"01")
I have tried different formats and formulas, but nothing seems to work in
this column. Could anyone come up with a formula to put here that would work
for me?

Thanks

If you look in the formula bar of any of the cells in Sheet 1 column J you
may see that by formatting the cell to (MM) you have converted the 01 data to
a date which is now represented as 01/01/1900, therefore your formula which
is looking for instances of 01 will find nothing, play with the format of
column J or alter the formula in sheet 2 thro 60 to look for the appropriate
date

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another sheet
within my workbook I calculate different information that I get from my main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01 occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2 column B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[David Biddulph]

One point to note is that by putting quote marks around the "01" in your
formula, you are looking for cells whose content is 01 as a text string. It
won't find numbers. If you're going to do it that way, you would need to
make your formula in column J look like:
=TEXT(H2-G2,"mm")

 

[Sapphyre]

Thanks for the reply bj. I tried your suggestion and it didn't work. I'll
keep at it though...I know there has to be a solution

maybe
=sumproduct(--(minute(Sheet1!J2:J2500)=1))



9

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another sheet
within my workbook I calculate different information that I get from my main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01 occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2 column B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[Sapphyre]

Thank you for the reply David. I originaly had my formula for J set up like
what you describe (=Text), but that is where the problem started, it wouldn't
calculate the subtraction of H from G correctly. It worked great unless it
was over 24 hours or crossed midnight.

One point to note is that by putting quote marks around the "01" in your
formula, you are looking for cells whose content is 01 as a text string. It
won't find numbers. If you're going to do it that way, you would need to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another sheet
within my workbook I calculate different information that I get from my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2 column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[David Biddulph]

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or beyond 24
hours. It will give you the number of minutes of that time difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great unless
it
was over 24 hours or crossed midnight.

One point to note is that by putting quote marks around the "01" in your
formula, you are looking for cells whose content is 01 as a text string.
It
won't find numbers. If you're going to do it that way, you would need to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another
sheet
within my workbook I calculate different information that I get from my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[bj]

in what way didn't it work?
if you find an example in the j column which shows 1 (Jxx) what happens when
you use the equation
=minute(Jxx)
if it is not 1 what happens when you change the format of column J?

Thanks for the reply bj. I tried your suggestion and it didn't work. I'll
keep at it though...I know there has to be a solution

maybe
=sumproduct(--(minute(Sheet1!J2:J2500)=1))



9

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another sheet
within my workbook I calculate different information that I get from my main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01 occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2 column B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[Sapphyre]

Thank you David for your response. Ok, that did fix that problem. But oddly
only half of my Sheet2 B column respondes correctly to it. As you can see
I'm pretty new at this, so I'm sorry for all the questions.

Ok here is my problem now.
Sheet2, Column B:
As I mentioned before I use the formula =COUNTIF(Sheet1!J2:J2500,"2")
formatted general. And I use that through "59" (which is where it then turns
to hours). When it turns to hours I then use
=SUMPRODUCT(--(Sheet1!J2:J2500>="60"),--(Sheet1!J2:J2500<="119")
and increase the formula 1 hour in minutes each cell, ie.
=SUMPRODUCT(--(Sheet1!J2:J2532>=120),--(Sheet1!J2:J2532<=179))

The =COUNTIF is working but the SUMPRODUCT is not.

any idea's?

Thanks again to all that have helped me thus far.

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or beyond 24
hours. It will give you the number of minutes of that time difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great unless
it
was over 24 hours or crossed midnight.

One point to note is that by putting quote marks around the "01" in your
formula, you are looking for cells whose content is 01 as a text string.
It
won't find numbers. If you're going to do it that way, you would need to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my date/time
columns and my total minutes column on my main sheet1. On another
sheet
within my workbook I calculate different information that I get from my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right anymore
useing that above formula/format. I've tried formatting the sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours) and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[David Biddulph]

Read back through the previous replies and look at what you've done.
--
David Biddulph

Thank you David for your response. Ok, that did fix that problem. But
oddly
only half of my Sheet2 B column respondes correctly to it. As you can see
I'm pretty new at this, so I'm sorry for all the questions.

Ok here is my problem now.
Sheet2, Column B:
As I mentioned before I use the formula =COUNTIF(Sheet1!J2:J2500,"2")
formatted general. And I use that through "59" (which is where it then
turns
to hours). When it turns to hours I then use
=SUMPRODUCT(--(Sheet1!J2:J2500>="60"),--(Sheet1!J2:J2500<="119")
and increase the formula 1 hour in minutes each cell, ie.
=SUMPRODUCT(--(Sheet1!J2:J2532>=120),--(Sheet1!J2:J2532<=179))

The =COUNTIF is working but the SUMPRODUCT is not.

any idea's?

Thanks again to all that have helped me thus far.

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or beyond
24
hours. It will give you the number of minutes of that time difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great
unless
it
was over 24 hours or crossed midnight.

:

One point to note is that by putting quote marks around the "01" in
your
formula, you are looking for cells whose content is 01 as a text
string.
It
won't find numbers. If you're going to do it that way, you would need
to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my
date/time
columns and my total minutes column on my main sheet1. On another
sheet
within my workbook I calculate different information that I get from
my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right
anymore
useing that above formula/format. I've tried formatting the sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours)
and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[Sapphyre]

Ok I read back through it. I realize that I originaly changed my J column in
sheet1, and nothing on sheet2 would work. So when you suggested using the
=TEXT(H2-G2,"[mm]") to get it to calculate if more than 24 hours and if it
crosses midnight, I just put my sheet2 back the way it was, because then I
had a text formula again and it should work again. Unfortunately only 1/2 of
my sheet2 works.

So let me just present this as a new question so we are not confused by the
previous posts.

I have 2 sheets in my workbook.
Sheet1:
J column: =TEXT(H2-G2,"[mm]") format-General
Sheet2:
B column: =SUMPRODUCT(--(Sheet1!J2:J2500>=60),--(Sheet1!J2:J2500<=119))

Sheet1 J column calculates how many minutes a project took.
Sheet2 B column calculates how many occurances specific minutes took place.
So on this example above, the formula on sheet2 B column should calculate
how many times it took between 60 and 119 minutes (gotten from sheet1 J
column).

Any help would be appreciated. Once I get this one, I'm finally finished
with this spreadsheet.

Read back through the previous replies and look at what you've done.
--
David Biddulph

Thank you David for your response. Ok, that did fix that problem. But
oddly
only half of my Sheet2 B column respondes correctly to it. As you can see
I'm pretty new at this, so I'm sorry for all the questions.

Ok here is my problem now.
Sheet2, Column B:
As I mentioned before I use the formula =COUNTIF(Sheet1!J2:J2500,"2")
formatted general. And I use that through "59" (which is where it then
turns
to hours). When it turns to hours I then use
=SUMPRODUCT(--(Sheet1!J2:J2500>="60"),--(Sheet1!J2:J2500<="119")
and increase the formula 1 hour in minutes each cell, ie.
=SUMPRODUCT(--(Sheet1!J2:J2532>=120),--(Sheet1!J2:J2532<=179))

The =COUNTIF is working but the SUMPRODUCT is not.

any idea's?

Thanks again to all that have helped me thus far.

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or beyond
24
hours. It will give you the number of minutes of that time difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great
unless
it
was over 24 hours or crossed midnight.

:

One point to note is that by putting quote marks around the "01" in
your
formula, you are looking for cells whose content is 01 as a text
string.
It
won't find numbers. If you're going to do it that way, you would need
to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my
date/time
columns and my total minutes column on my main sheet1. On another
sheet
within my workbook I calculate different information that I get from
my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right
anymore
useing that above formula/format. I've tried formatting the sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24 hours)
and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[David Biddulph]

Column J is text. Your SUMPRODUCT is looking for numbers.

Two options to try:

Change J either to =--TEXT(H2-G2,"[mm]")
or to =(H2-G2)*24*60
--
David Biddulph

Ok I read back through it. I realize that I originaly changed my J column
in
sheet1, and nothing on sheet2 would work. So when you suggested using the
=TEXT(H2-G2,"[mm]") to get it to calculate if more than 24 hours and if it
crosses midnight, I just put my sheet2 back the way it was, because then I
had a text formula again and it should work again. Unfortunately only 1/2
of
my sheet2 works.

So let me just present this as a new question so we are not confused by
the
previous posts.

I have 2 sheets in my workbook.
Sheet1:
J column: =TEXT(H2-G2,"[mm]") format-General
Sheet2:
B column: =SUMPRODUCT(--(Sheet1!J2:J2500>=60),--(Sheet1!J2:J2500<=119))

Sheet1 J column calculates how many minutes a project took.
Sheet2 B column calculates how many occurances specific minutes took
place.
So on this example above, the formula on sheet2 B column should calculate
how many times it took between 60 and 119 minutes (gotten from sheet1 J
column).

Any help would be appreciated. Once I get this one, I'm finally finished
with this spreadsheet.

Read back through the previous replies and look at what you've done.
--
David Biddulph

Thank you David for your response. Ok, that did fix that problem. But
oddly
only half of my Sheet2 B column respondes correctly to it. As you can
see
I'm pretty new at this, so I'm sorry for all the questions.

Ok here is my problem now.
Sheet2, Column B:
As I mentioned before I use the formula =COUNTIF(Sheet1!J2:J2500,"2")
formatted general. And I use that through "59" (which is where it then
turns
to hours). When it turns to hours I then use
=SUMPRODUCT(--(Sheet1!J2:J2500>="60"),--(Sheet1!J2:J2500<="119")
and increase the formula 1 hour in minutes each cell, ie.
=SUMPRODUCT(--(Sheet1!J2:J2532>=120),--(Sheet1!J2:J2532<=179))

The =COUNTIF is working but the SUMPRODUCT is not.

any idea's?

Thanks again to all that have helped me thus far.


:

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or
beyond
24
hours. It will give you the number of minutes of that time
difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set
up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great
unless
it
was over 24 hours or crossed midnight.

:

One point to note is that by putting quote marks around the "01" in
your
formula, you are looking for cells whose content is 01 as a text
string.
It
won't find numbers. If you're going to do it that way, you would
need
to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my
date/time
columns and my total minutes column on my main sheet1. On
another
sheet
within my workbook I calculate different information that I get
from
my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right
anymore
useing that above formula/format. I've tried formatting the
sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24
hours)
and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre

 

[Sapphyre]

Thank you so much David. Putting the -- in front of my formula did the
trick.

Thank you all for the help with getting my spreadsheet working.

Column J is text. Your SUMPRODUCT is looking for numbers.

Two options to try:

Change J either to =--TEXT(H2-G2,"[mm]")
or to =(H2-G2)*24*60
--
David Biddulph

Ok I read back through it. I realize that I originaly changed my J column
in
sheet1, and nothing on sheet2 would work. So when you suggested using the
=TEXT(H2-G2,"[mm]") to get it to calculate if more than 24 hours and if it
crosses midnight, I just put my sheet2 back the way it was, because then I
had a text formula again and it should work again. Unfortunately only 1/2
of
my sheet2 works.

So let me just present this as a new question so we are not confused by
the
previous posts.

I have 2 sheets in my workbook.
Sheet1:
J column: =TEXT(H2-G2,"[mm]") format-General
Sheet2:
B column: =SUMPRODUCT(--(Sheet1!J2:J2500>=60),--(Sheet1!J2:J2500<=119))

Sheet1 J column calculates how many minutes a project took.
Sheet2 B column calculates how many occurances specific minutes took
place.
So on this example above, the formula on sheet2 B column should calculate
how many times it took between 60 and 119 minutes (gotten from sheet1 J
column).

Any help would be appreciated. Once I get this one, I'm finally finished
with this spreadsheet.

Read back through the previous replies and look at what you've done.
--
David Biddulph

Thank you David for your response. Ok, that did fix that problem. But
oddly
only half of my Sheet2 B column respondes correctly to it. As you can
see
I'm pretty new at this, so I'm sorry for all the questions.

Ok here is my problem now.
Sheet2, Column B:
As I mentioned before I use the formula =COUNTIF(Sheet1!J2:J2500,"2")
formatted general. And I use that through "59" (which is where it then
turns
to hours). When it turns to hours I then use
=SUMPRODUCT(--(Sheet1!J2:J2500>="60"),--(Sheet1!J2:J2500<="119")
and increase the formula 1 hour in minutes each cell, ie.
=SUMPRODUCT(--(Sheet1!J2:J2532>=120),--(Sheet1!J2:J2532<=179))

The =COUNTIF is working but the SUMPRODUCT is not.

any idea's?

Thanks again to all that have helped me thus far.


:

Sorry, I should have had
=TEXT(H2-G2,"[mm]") (with the square brackets)

That will cope with time differences which go across midnight, or
beyond
24
hours. It will give you the number of minutes of that time
difference.
--
David Biddulph

Thank you for the reply David. I originaly had my formula for J set
up
like
what you describe (=Text), but that is where the problem started, it
wouldn't
calculate the subtraction of H from G correctly. It worked great
unless
it
was over 24 hours or crossed midnight.

:

One point to note is that by putting quote marks around the "01" in
your
formula, you are looking for cells whose content is 01 as a text
string.
It
won't find numbers. If you're going to do it that way, you would
need
to
make your formula in column J look like:
=TEXT(H2-G2,"mm")
--
David Biddulph

Ok, I finally got a formula/format to work for subtracting my
date/time
columns and my total minutes column on my main sheet1. On
another
sheet
within my workbook I calculate different information that I get
from
my
main
sheet.

This is how I use to have my sheet2 column B
=COUNTIF(Sheet1!J2:J2500,"01") formatted-General
This would calculate how many times column J from Sheet1 had a 01
occurance.
I did this all the way through 60.
But now that I have changed my sheet1 J column with this formula
=H2-G2 and formatted [mm] it won't calculate on my sheet2 right
anymore
useing that above formula/format. I've tried formatting the
sheet2
column
B
with [mm] but that doesn't work either.

It's like I fix one problem (subtracting date/times <,=,>24
hours)
and
created a new problem .

It would be most appreciated if someone could help me with this.

Thank you so much,
Sapphyre