Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
I used
=SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
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)) ")) In the future, using the correct terminology will get you better responses. F, G, J and K are columns, not tabs. Regards, Fred "Neri" wrote in message ... I used =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
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. "Fred Smith" wrote: 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)) ")) In the future, using the correct terminology will get you better responses. F, G, J and K are columns, not tabs. Regards, Fred "Neri" wrote in message ... I used =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
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. "Neri" wrote in message ... 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. "Fred Smith" wrote: 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)) ")) In the future, using the correct terminology will get you better responses. F, G, J and K are columns, not tabs. Regards, Fred "Neri" wrote in message ... I used =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
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)) ")) HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) Take out any line breaks that your news reader or web interface puts in... HTH, Bernie MS Excel MVP "Neri" wrote in message ... im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, ") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! 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! "Fred Smith" wrote: 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. "Neri" wrote in message ... 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. "Fred Smith" wrote: 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)) ")) In the future, using the correct terminology will get you better responses. F, G, J and K are columns, not tabs. Regards, Fred "Neri" wrote in message ... I used =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compute date and time difference
Guess what? I already figured it out. Thanks anyway!
"Neri" wrote: 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)) ")) HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) Take out any line breaks that your news reader or web interface puts in... HTH, Bernie MS Excel MVP "Neri" wrote in message ... im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, ") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! 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! "Fred Smith" wrote: 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. "Neri" wrote in message ... 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. "Fred Smith" wrote: 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)) ")) In the future, using the correct terminology will get you better responses. F, G, J and K are columns, not tabs. Regards, Fred "Neri" wrote in message ... I used =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) ")) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date time difference | Excel Discussion (Misc queries) | |||
Difference in date and time | Excel Discussion (Misc queries) | |||
Date and time difference | Excel Worksheet Functions | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
compute the difference between two dates accurately | Excel Discussion (Misc queries) |