CODEWITHSUNDEEP
excelexcel-formula
dreed75
dreed75

Reputation: 117

Excel - Formula to filter column based on variable time

I have a column that needs split based on "morning" and "evening" although the morning and evening times move every day (it's based on sidereal day). Calling them morning and evening is a little deceiving though because eventually the time will creep into the next day and I want to keep the groups distinct. It is more accurate to call them group 1 and group 2. It just so happens that they are around 12 hours apart so it looks like you can just separate based on time of day but once the later group creeps into the AM hours, it would start to get counted as "morning" and the earlier group would roll into the afternoon and be counted as "afternoon" See screenshot below for example data.

screenshot

I need them split so I can perform operations on the value column so I can distinguish the values in the first group from the values in the second group. I thought of doing some sort of flip flop algorithm based on the previous cell but there may be a more elegant way to do it. Also, it's not shown on the example data but sometimes the day may skip but the times more or less continue in the same pattern of increasing by 3-5 minutes each day.

Upvotes: 0

Views: 122

Answers (1)

teylyn
teylyn

Reputation: 35915

A date with a time stamp is stored as a number in Excel. Days are stored as whole numbers, time is stored in decimals. So, disregarding the date part, look at the decimal of the number and determine if that is before or after the time you want.

0.5 for example is midday, or 12 noon. So if the decimal part of A1 is less than 0.5, the time stamp would be in the morning.

=if(A1-int(A1)<0.5,"before noon","after noon")

It is not clear from your question how sidereal relates to the data in your sample.

Upvotes: 1

Related Questions