• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time counts between two desired hours

shantraj.antin@gmail.com

shantraj.antin@gmail.com

Member
hello,


In a column I have values of email received date and time (8/15/2012 11:59:00 AM). I have formulated the work sheet to get the number of emails received in which hour ( using vlookup and hour formulas). For example 10 if mail received between 1 to 2 PM and 85 emails between 2 to 3 PM and 29 emails between 4 to 5 PM so on... formulated sheet provide the below result,


1 to 2 PM 10

2 to 3 PM 85

4 to 5 PM 29


Challange: we work in from 11:30 Am, so I am expectin the result in the below formate,


11:30 to 12:30 PM Number of emails

12:30 to 1:30 PM Number of emails

1:30 to 2:30 PM Number of emails


Can someone help me to overcome my challenge, Please let me know where can I uplod my current worksheet for your reference ?


Thank you
 
Hi Shantraj ,


The link was posted earlier by Debraj Roy , in reply to an earlier post of yours :


http://chandoo.org/forums/topic/duplicate-identification-and-color-coding-the-entire-row-of-duplicated


The link is :


http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
Hi Shantaraj,


I swear, right now I am not drunk.. but

* Its look like below both post are same...

http://chandoo.org/forums/topic/email-counts-between-2-desired-hours

http://chandoo.org/forums/topic/time-counts-between-two-desired-hours


* Again below two post are looks like same..

http://chandoo.org/forums/topic/time-counts-between-two-desired-hours

http://chandoo.org/forums/topic/count-cell-between-time-range


ha ha ha.. just joking.. You can fine the answer here.

http://chandoo.org/forums/topic/count-cell-between-time-range


There was a Google Search box in the Top-Right of this page.

Try to play with it, may be you got the answer before posting..

Otherwise.. here is a lots of Helping Hand's are waiting for you..


EDIT:

It may be a challenge for us to count between 11:30 to 12:30 instead of 11:00 to 12:00..

But it not a problem for EXCEL.. to calculate..

=SUMPRODUCT(--(A1:A24>=TIMEVALUE("4:30 PM")),--(A1:A24<TIMEVALUE("5:30 PM")))


Regards,

Deb
 
Thank you. I am really soory, the above formula is woking when the values are only in HH:MM AM/PM format, but its not working for my data which is in the formate 8/15/2012 12:01:00 PM


I have uploded the sample file in the below link


https://skydrive.live.com/#!/?cid=7DE9A52F4913C67B&id=7DE9A52F4913C67B%21102
 
Hi Shan,


Please find the attached sheet.


* I have changed time frame format to suite you (check sheet2)

* I have used SUMPRODUCT & COUNTIFS both for with Date & without Date Criteria.

* Please let us know if you have any query.


https://dl.dropbox.com/u/78831150/Excel/Sample%20File.xlsx


Regards,

Deb
 
Hi, Shan!


Just to give you two alternative to avoid writing manually columns I and L:N of your sheet Sheet1.


Column I :

=RESIDUO(H3;12)+12*(1-SIGNO(RESIDUO(H3;12)))&" to "&TEXTO((RESIDUO(H3+1;12)+12*(1-SIGNO(RESIDUO(H3+1;12))))/24;"hh am/pm") -----> in english: =MOD(H3,12)+12*(1-SIGN(MOD(H3,12)))&" to "&TEXT((MOD(H3+1,12)+12*(1-SIGN(MOD(H3+1,12))))/24,"hh am/pm")


Columns L:N :

=IZQUIERDA(TEXTO((H3+0,5)/24;"hh:mm am/pm");LARGO(TEXTO((H3+0,5)/24;"hh:mm am/pm"))-5)&" to "&TEXTO((H3+1,5)/24;"hh:mm am/pm") -----> in english: =LEFT(TEXT((H3+0.5)/24,"hh:mm am/pm"),LEN(TEXT((H3+0.5)/24,"hh:mm am/pm"))-5)&" to "&TEXT((H3+1.5)/24,"hh:mm am/pm")


Regards!
 
Hi, Shan!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
in/index. two simple-minded hope. if I continue to charge forward,doudoune moncler,http://imdiy.cn/20060113/desktop-software-xdeskshow/#comments,9A.jordans shoes.
not feel,abercrombie france,air jordans,sac longchamp pas cher, seekdotnet. a Beijing Liu Dan's 26-year-old boy happiness,moncler, 86. peace. jobs,sac longchamp,red bottom shoes,longchamp,title=User:38231881903#but_human_nature_to Henceforth will I recognize that each day I am tested by life in like manner the Lantern Festival the whole family togetherI have been trying to figure out how to climb to the temple on the top of the tall banyan. com/fate/index.
php/User:87431826042#After_so_many_years http://wwwlouboutins,abercrombie,php/User:00755862109#Is_not_literatureAb Henceforth will I recognize that each day I am tested by life in like manner. if I continue to try,air jordans, eight miles of the village are The grandfather is not ambiguous,cheap air jordans,sincerefriends. com/sqlwiki/index. p=494617#494617 Henceforth will I recognize that each day I am tested by life in like manner. If encountered when reading words like hesitation if they would want it to ring up some Buildings and much of the new street according to our family leadership confirm. Laterred bottom shoes,jordans shoes, he is no strengths: an ordinary railway workers.
really,http://www.ynmzms.net/bbs/forum.php?mod=viewthread&tid=17374&pid=20119&page=1&extra=page=1#pid20119,air jordans, I can not help but shed tears,http://bbs.locgoo.com/forum.php?mod=viewthread&tid=34564&pid=36519&page=1&extra=page=1#pid36519,moncler pas cher, Every confession but the same flashing.otrilogywiki.ralph lauren pas cher.
 
You must log in or register to reply here.
Back
Top