Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Formulas
I am trying to find out if something occurred between certain hours of the
day. I am looking at 1 hour time bands between 5 pm and 6 am, which is between 17:00 and 6:00. I need a formula that will compare the start and end times (between 17:00 and 6:00, and return a True value if it occurs in each one hour time band. For example, I want a True returned if something occurred between 17:00 and 18:00, in which case that something's actual times are 17:15 and 17:45. So since these times are between that 17:00 and 18:00, I want a True entered. How can I write a formula for this? Also, is there any exception to the formula when going past 24:00? thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Formulas
If your input times are from A2 down column A, and the start times of your
one-hour slots are from B1 across row 1, then use =HOUR($A2)=HOUR(B$1) to return TRUE or FALSE or =IF(HOUR($A2)=HOUR(B$1),TRUE,"") if you want TRUE or blank. -- David Biddulph "Daren" wrote in message ... I am trying to find out if something occurred between certain hours of the day. I am looking at 1 hour time bands between 5 pm and 6 am, which is between 17:00 and 6:00. I need a formula that will compare the start and end times (between 17:00 and 6:00, and return a True value if it occurs in each one hour time band. For example, I want a True returned if something occurred between 17:00 and 18:00, in which case that something's actual times are 17:15 and 17:45. So since these times are between that 17:00 and 18:00, I want a True entered. How can I write a formula for this? Also, is there any exception to the formula when going past 24:00? thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Formulas
This =AND(HOUR(A1)=7,HOUR(B1)=8) gives you a start
If B3 is displaying 24:15 by virtue of a custom format [h]:mm then I would use =AND(HOUR(A2)=23,HOUR(B1)=0) This will also work =AND(HOUR(A1)=D1,HOUR(B1)=E1) when D1 and E1 hold values 7 and 8. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Daren" wrote in message ... I am trying to find out if something occurred between certain hours of the day. I am looking at 1 hour time bands between 5 pm and 6 am, which is between 17:00 and 6:00. I need a formula that will compare the start and end times (between 17:00 and 6:00, and return a True value if it occurs in each one hour time band. For example, I want a True returned if something occurred between 17:00 and 18:00, in which case that something's actual times are 17:15 and 17:45. So since these times are between that 17:00 and 18:00, I want a True entered. How can I write a formula for this? Also, is there any exception to the formula when going past 24:00? thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Formulas
Thanks. I also need to know how much time was used in the one hour time
band. Is there a function that can help with that? thanks. "Bernard Liengme" wrote: This =AND(HOUR(A1)=7,HOUR(B1)=8) gives you a start If B3 is displaying 24:15 by virtue of a custom format [h]:mm then I would use =AND(HOUR(A2)=23,HOUR(B1)=0) This will also work =AND(HOUR(A1)=D1,HOUR(B1)=E1) when D1 and E1 hold values 7 and 8. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Daren" wrote in message ... I am trying to find out if something occurred between certain hours of the day. I am looking at 1 hour time bands between 5 pm and 6 am, which is between 17:00 and 6:00. I need a formula that will compare the start and end times (between 17:00 and 6:00, and return a True value if it occurs in each one hour time band. For example, I want a True returned if something occurred between 17:00 and 18:00, in which case that something's actual times are 17:15 and 17:45. So since these times are between that 17:00 and 18:00, I want a True entered. How can I write a formula for this? Also, is there any exception to the formula when going past 24:00? thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences Formulas
Ok, I will try that. I also need to know how much time was used in that one
hour time band. I would think a If-And statement would work, but am not sure. Can you advise further? thanks. "David Biddulph" wrote: If your input times are from A2 down column A, and the start times of your one-hour slots are from B1 across row 1, then use =HOUR($A2)=HOUR(B$1) to return TRUE or FALSE or =IF(HOUR($A2)=HOUR(B$1),TRUE,"") if you want TRUE or blank. -- David Biddulph "Daren" wrote in message ... I am trying to find out if something occurred between certain hours of the day. I am looking at 1 hour time bands between 5 pm and 6 am, which is between 17:00 and 6:00. I need a formula that will compare the start and end times (between 17:00 and 6:00, and return a True value if it occurs in each one hour time band. For example, I want a True returned if something occurred between 17:00 and 18:00, in which case that something's actual times are 17:15 and 17:45. So since these times are between that 17:00 and 18:00, I want a True entered. How can I write a formula for this? Also, is there any exception to the formula when going past 24:00? thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting events since last occurences | Excel Worksheet Functions | |||
Counting the occurences | Excel Worksheet Functions | |||
Counting Occurences of Hours of the Day | Excel Discussion (Misc queries) | |||
Counting Occurences | Excel Discussion (Misc queries) | |||
Counting Number of Occurences | Excel Discussion (Misc queries) |