Steve Steve
asked on
Excel - Month value then if Date is Night or Day
I have several excel sheets each containing the same information in the same columns but need to work out a formula to determine if a time is day or night, depending on the month it is within, based on hours of daylight within the month as this will vary throughout the year in the UK.
Example
month time outcome
11/02/2013 16:03 Day
14/02/2013 02:10 Night
21/03/2013 22:00 Night
20/03/2013 10:03 Day
01/10/2014 04:34 Night
06/06/2013 04:35 Day
I need a formula to calculate if a date is a given month then using defined times within that month, decide if it is day or night depending on the time.
January for example will be darker at 5am than in August (Night) and October at 4pm will be (Night) than lets say in March (Day)
I have included an example.
Columns A,B,C has the data, Columns H-L gives an example of the time bands I need to identify for the full year.
Ideally I need it summarising to:
Defect Day Night Total
DBHS 21 10 31
DBTL 03 13 16
FLOD 00 28 28
Thanks in anticipation..
Example.xlsx
Example
month time outcome
11/02/2013 16:03 Day
14/02/2013 02:10 Night
21/03/2013 22:00 Night
20/03/2013 10:03 Day
01/10/2014 04:34 Night
06/06/2013 04:35 Day
I need a formula to calculate if a date is a given month then using defined times within that month, decide if it is day or night depending on the time.
January for example will be darker at 5am than in August (Night) and October at 4pm will be (Night) than lets say in March (Day)
I have included an example.
Columns A,B,C has the data, Columns H-L gives an example of the time bands I need to identify for the full year.
Ideally I need it summarising to:
Defect Day Night Total
DBHS 21 10 31
DBTL 03 13 16
FLOD 00 28 28
Thanks in anticipation..
Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I was looking for, huge thanks for taking the time to respond so quickly.
Saurabh...
Example.xlsx