Hi,
I have been trying to find a formula that will allow me to find out how many time a cretin hour appears in a spread sheet. I can seem to make the blow formula work. Any ideas?
COUNTIF(B$3:B$142,">=23:00:00<24:00:00") - this would help me count everything between 22:00:00
Try
=COUNTIFS(B$3:B$142,">=23:00:00",B$3:B$142,"<24:00:00")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks! looks way better but I am still getting 0's
Your times are all stored as text.
Select each column in turn, Data > Text to Columns, Finish.
Or select them all, do Alt+F11 to open the VBE, and in the Immediate window, paste Selection.Value = Selection.Value and hit Enter.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Your time entries in column B to AF are not entered as numbers, but as text.
You would have to either select column B and go to Data|Text to Columns and click Finish and repeat for each column...
.. or you would enter a 0 in a blank cell, copy it and to Edit|Paste Special and select Add... but this will enter a 0 in all empty cells in your selection which you would have to clean after...
the first method, won't put 0's in empty cells, but is more tediious.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I have selected each column and the selected Data>Text to column>Next >Next>Finish and still no go. Am I missing a step perhaps? I can now see it adding an AM or PM where it should so it should be formatted.
Applying shg's much quicker method, I got the attached.
I put my formula also in AI3
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This may be oversimplification, but you could do a ctrl+f and (find all) bottom left corner will report how many entries there are.
How does that solve the OP's specific question?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I understood "find out how many time a cretin hour appears in a spread sheet" to be how many times it appears in any cell in the sheet.
A find for "4 hours (format not important)" would yield how many times a certain amount cells where "4 hours" appears in the spread sheet.
my apologies for junking the thread, i did not see his example.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks