• 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.

Email counts between 2 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
 
Check out the 2nd green stickey for info on how to upload a workbook.


As for your question, it's just a matter of changing the lookup value. XL treats time as a decimal portion to a number. Aka, 1 hr = 1/24. One minute is 1/24/60 (which is equal to 1/1440). So, to create the "value" of today's date at 1:30 PM, that value is:

=TODAY()+13/24+30/1440


Knowing that, I would suspect that you can modify the formulas you already have in place to look at the entire time, rather than just the hour portion.
 
Thanks Luke,


but I am not too sure how to modify the formula in context to =TODAY()+13/24+30/1440


I have uploded the sample file @ Sky Drive. You responce is much appreciated. thank you.
 
Hi Shan,


You missed to read a line on posting rule..

Once you have uploaded the file, the web site should give you a Download Link.

Copy the link and paste it in a post with details of what you are trying to achieve
Click to expand...

BTW, Did you checked this post ..

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

Regards,

Deb
 
Thread closed as it is continued in another thread:

http://chandoo.org/forums/topic/time-counts-between-two-desired-hours
 
You must log in or register to reply here.
Back
Top