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!)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Common Pipistrelle[/TD]
[TD]Soprano Pipistrelle[/TD]
[TD]Brown long-eared[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:01[/TD]
[TD]36[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:02[/TD]
[TD]57[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:03[/TD]
[TD]32[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:07[/TD]
[TD]31[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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!)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Common Pipistrelle[/TD]
[TD]Soprano Pipistrelle[/TD]
[TD]Brown long-eared[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:01[/TD]
[TD]36[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:02[/TD]
[TD]57[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:03[/TD]
[TD]32[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:07[/TD]
[TD]31[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]