LinkBack URL
About LinkBacks
Guys just a quick one,
in cell F5 I would like to know how to count the times between 13:00 am and 14:00 so if theres 3 times it will show a 3 i havnt got a clue what formula to use thanks anybody who can help
Guys just a quick one,
in cell F5 I would like to know how to count the times between 13:00 am and 14:00 so if theres 3 times it will show a 3 i havnt got a clue what formula to use thanks anybody who can help
Last edited by dodger999; 08-26-2009 at 07:39 AM.
Based purely on your sample file, one method might be:
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for the quick reply but being the novice i am im a bit lost even though it does work i dont understand it sorry
It counts the number of entries in B where the HOUR of said entry equates to the requisite value be it 11, 12 etc...
The requisite hour (11, 12, 13 etc...) is determined by taking 10 and adding to that the number of columns in the specified range... in the first column that increment will be 1 (one column in range $D5:D5) therefore hour 11, when the formula is copied to E5 the increment will become 2 given there are two columns present in the range $D5:E5 and therefore making hour = 12 ... and so on and so forth.
For more info. on SUMPRODUCT and coercion (--) refer to the link in my signature.
Many thanks for the explanation
Sorry 1 other problem thats just hit me is i should have put in AM times i:e cells should read 00:01 after midnight so 13:55 should really be 01:55 how do i get around this problem sorry to be a pain
It shouldn't matter. eg. HOUR(1:00 PM) will be 13 and HOUR(1:00 AM) will be 1. Is it not working for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
It is working in + mode and i do understand more of what you explained but i need to go into AM now as you can see from my amended sheet im lost again
Based on your revised setup such that first calculation (D5) is for 11:00 PM then:
Please Login or Register to view this content.
Ok i had the same result without using mod i am using a larger database than shown so when i extend b5:b11 to B5:B30 say I get 23 as the result it seems now to be counting the blank cells this is head banging im so sorry
Yes, that's because HOUR(blank) = 0 ... so based on the latest version:
It might be easier to use a Pivot Table ...Please Login or Register to view this content.
Thanks for your time it worked very well on the small sheet but when copied to my larger sheet nothing so i think i should give up just cant see whats wrong anywhere
This is last hope here is my dummy sheet in full just to show if anything is wrong
The values in J are not numbers per se - they are numbers stored as text.
Based on the latest file:
Please Login or Register to view this content.
All works apart from 12:00 (Q80) this is so strange any ideas? this is the sheet im using it worked on the dummy sheet because it was text but i need formulas in them as you can see from this sheet
Last edited by dodger999; 08-27-2009 at 04:01 PM.
The issue is that the values that appear to be blank (eg J93 etc) do in fact contain the value 0 it's just that your display options are set that zero does not display.
Based then on the fact that valid results are Text (ie the zeroes imply not time) and again based purely on the last file:
Please Login or Register to view this content.
i suppose the only other way is to do a countif formula for the 12:00 cell but that would mean 59 formulas
I confess I no longer have any idea what the issue is ... the last formula will, based on your file, generate numbers as requested.
You cracked it cant believe it thanks a million for you patience it was the text i needed i didnt even no it exsisted i will put solved on thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks