| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conitif doesn't recognise time format? how to get round this?
i am tring to get excel to count the number of entries of certain times and a
worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but its because the details that it is looking at are in time format. I dont want to have to manualy change all the times to text which would work but takes to long. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
You could use
=COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
i am looking for the number of entries between eg 06:00 and 07:00 or 06:59.
"" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
So presumably you have changed "" to "=" ?
-- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
i am newish at excel and am still learning. i have changed that but it gives
me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
If you're struggling to debug your formula, split it up into manageable
chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
The following formula should count the number of records with times from
6:00pm through 6:59pm: =SUMPRODUCT((HOUR(D3:D1500)=18)*(HOUR(D3:D1500)<1 9)) If you don't want to include 6:00pm itself, try this version: =SUMPRODUCT((HOUR(D4:D1501)=18)*(HOUR(D4:D1501)<1 9)*(MINUTE(D3:D1500)0)) Hope this helps, Hutch "mavgn" wrote: i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
i need the result to be numbers/times = to 06:00 but not more than 07:00.
i am going through 24hrs worth of data and want to break it up into scan's per 1hr "David Biddulph" wrote: If you're struggling to debug your formula, split it up into manageable chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
You didn't answer my question.
-- David Biddulph "mavgn" wrote in message ... i need the result to be numbers/times = to 06:00 but not more than 07:00. i am going through 24hrs worth of data and want to break it up into scan's per 1hr "David Biddulph" wrote: If you're struggling to debug your formula, split it up into manageable chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
david,
it seems it is counting everything after the 06:00 or 07:00 which is what i tried to say on my last message. Tom i have also tried your reply i have never used sumproduct before and have had results within what i am looking for. i am still having some difficulties but i have to go now till tomorrow afternoon. thank you both for your help so far. "David Biddulph" wrote: You didn't answer my question. -- David Biddulph "mavgn" wrote in message ... i need the result to be numbers/times = to 06:00 but not more than 07:00. i am going through 24hrs worth of data and want to break it up into scan's per 1hr "David Biddulph" wrote: If you're struggling to debug your formula, split it up into manageable chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
I notice the cell references were off in the second formula I posted. Here is
a corrected version: =SUMPRODUCT((HOUR(D3:D1500)=18)*(HOUR(D3:D1500)<1 9)*(MINUTE(D3:D1500)0)) If you have never used SUMPRODUCT, here is a link to a good introduction to it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hutch "mavgn" wrote: david, it seems it is counting everything after the 06:00 or 07:00 which is what i tried to say on my last message. Tom i have also tried your reply i have never used sumproduct before and have had results within what i am looking for. i am still having some difficulties but i have to go now till tomorrow afternoon. thank you both for your help so far. "David Biddulph" wrote: You didn't answer my question. -- David Biddulph "mavgn" wrote in message ... i need the result to be numbers/times = to 06:00 but not more than 07:00. i am going through 24hrs worth of data and want to break it up into scan's per 1hr "David Biddulph" wrote: If you're struggling to debug your formula, split it up into manageable chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conitif doesn't recognise time format? how to get round this?
Sorry, this is like drawing teeth. We're trying to help you, but we can't
help you if you won't answer the simple questions and if you steadfastly persist in withholding the information which we'd need to be able to help you. Hopefully you can sort it out for yourself. -- David Biddulph "mavgn" wrote in message ... david, it seems it is counting everything after the 06:00 or 07:00 which is what i tried to say on my last message. Tom i have also tried your reply i have never used sumproduct before and have had results within what i am looking for. i am still having some difficulties but i have to go now till tomorrow afternoon. thank you both for your help so far. "David Biddulph" wrote: You didn't answer my question. -- David Biddulph "mavgn" wrote in message ... i need the result to be numbers/times = to 06:00 but not more than 07:00. i am going through 24hrs worth of data and want to break it up into scan's per 1hr "David Biddulph" wrote: If you're struggling to debug your formula, split it up into manageable chunks. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,)) gives you the number greater than or equal to 06:00 =COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) gives you the number greater than or equal to 07:00 Which of those gives a number different from what you expected? -- David Biddulph "mavgn" wrote in message ... i am newish at excel and am still learning. i have changed that but it gives me "06/08/1902 00:00" as an answer? i change that to genral format and it changes to 949 which i know is way over what i should be looking for. =COUNTIF(mon!$D$3:$D$1500,"="&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,)) "David Biddulph" wrote: So presumably you have changed "" to "=" ? -- David Biddulph "mavgn" wrote in message ... i am looking for the number of entries between eg 06:00 and 07:00 or 06:59. "" is grater than but i need between "David Biddulph" wrote: You could use =COUNTIF(mon!$D$3:$D$1500,""&6/24)-COUNTIF(mon!$D$3:$D$1500,"="&7/24) or =COUNTIF(mon!$D$3:$D$1500,""&TIME(6,,))-COUNTIF(mon!$D$3:$D$1500,"="&TIME(7,,))I am assuming that you have only times in the cells, not dates and timestogether merely formatted as times.--David Biddulph"mavgn" wrote in ...i am tring to get excel to count the number of entries of certain times anda worksheet. but the format is time eg 06:15 and i want it to look for the number of entries from "06". can anyone help. =countif(mon!$D$3:$D$1500,"06*") is what i have got to so far but itsbecause the details that it is looking at are in time format. I dont want to haveto manualy change all the times to text which would work but takes to long. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Round/Display (HR:MN:SEC) data in decimal HR format for Time C | Excel Discussion (Misc queries) | |||
| How do I round time | Excel Worksheet Functions | |||
| excel can no longer recognise format | Excel Discussion (Misc queries) | |||
| Round UP Time | Excel Worksheet Functions | |||
| How do I round time? | Excel Worksheet Functions | |||
Linear Mode
