compute date and time difference

N

Neri

I used
=SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000))
*(DATA!$L$2:$L$5000="(e-mail address removed)")) to compute if a person passed or
failed in our activity. the problem now is that, I didn't noticed the date
and time they submitted their reports.

For ex.

F G

1/2/2008 4:00:00 PM 1/2/2008 6:00:00 PM


J K
1/2/2008 4:00:00 PM 1/2/2008 5:00:00 PM


F and G tab are for the planned start and end period and the J and K tab are
for the actual start and end period. If I use the formula above, the person
will fail because the difference of time is not equal wherein the person
should pass bec he submitted his report 1hr ahead of his planned time. How am
I supposed to calculate the difference of the date and time? can you please
help me. Thanks!

*please also considered the date period
 
F

Fred Smith

Don't you simply want "<=" as opposed to just "="? Try:
=SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)<=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000))
*(DATA!$L$2:$L$5000="(e-mail address removed)"))

In the future, using the correct terminology will get you better responses.
F, G, J and K are columns, not tabs.

Regards,
Fred
 
N

Neri

Oh, im sorry. my mistake for the "tab" part.

I tried using "<=" but it doesn't compute the date and time difference.
I also tried using the TEXT command but still doesn't work.
 
F

Fred Smith

Well, I think it's your data, not the formula. I don't see how using Text
would be of any value. How do you know from the formula provided doesn't
calculate the time difference?

We first of all have to determine what kind of data you have before we can
come up with the proper formula.

Regards,
Fred.
 
N

Neri

Here's what we did last wed with Bernie Deitrick:

Ooops.

And for failed, change the = to <>:
=SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)<>
(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000))
*(DATA!$L$2:$L$5000="(e-mail address removed)"))

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Neri,

=SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000))
*(DATA!$L$2:$L$5000="(e-mail address removed)"))

Take out any line breaks that your news reader or web interface puts in...

HTH,
Bernie
MS Excel MVP




It calculates if a person passed or failed in our activity by checking if
the (planned end - planned start) is equal or not equal to (actual end -
actual start). The problem now is, what if the planned time is greater than
the actual time? it will result to failed which infact it must passed bec the
person submit his/her report earlier than the planned time. Please help me!
thanks!
 

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