Hi!
I have a large group of data which has number of bat calls (split by species) and then the time of each the calls.
I would like to group the calls into 15 minute periods in relation to sunset eg 15 minutes before sunset, 0-15 minutes after sunset, 15-30 minutes after sunset, 30-45 mins after and so on.
Is there an formula i can use to group the data in this way?
The next bit which i have struggled with is that when it goes past midnight, i would still like it to continue, however currently whatever i try excel is seeing it as a the next day and getting the time completely wrong.
For example if a have a bat call at 4am and sunset was at 7pm the night before excel is registering it as being 15hrs early rather than 9 hours later.
Here is a example version of what i have currently (but a lot less!)
<tbody>
</tbody>
I have a large group of data which has number of bat calls (split by species) and then the time of each the calls.
I would like to group the calls into 15 minute periods in relation to sunset eg 15 minutes before sunset, 0-15 minutes after sunset, 15-30 minutes after sunset, 30-45 mins after and so on.
Is there an formula i can use to group the data in this way?
The next bit which i have struggled with is that when it goes past midnight, i would still like it to continue, however currently whatever i try excel is seeing it as a the next day and getting the time completely wrong.
For example if a have a bat call at 4am and sunset was at 7pm the night before excel is registering it as being 15hrs early rather than 9 hours later.
Here is a example version of what i have currently (but a lot less!)
Time | Common Pipistrelle | Soprano Pipistrelle | Brown long-eared | |
19:01 | 36 | 1 | 1 | |
19:02 | 57 | 7 | 0 | |
19:03 | 32 | 6 | 0 | |
19:07 | 31 | 12 | 4 |
<tbody>
</tbody>