Convert date/time to reporting period

[West Man]

I am close but not quite there. I have date/times that need to be converted into reporting periods. Reporting periods will be 1, 2 etc.
Period 1 is an exception it begins at start of day on 6/6 and runs thru 11:59:59 the same day.
Period 2 then begins at 12:00:00 (noon) on 6/6 and runs thru 11:59:59 on 6/7.
Subsequent periods are like period 2 beginning at noon running thru 11:59:59 the following day.

I am close with =ROUNDDOWN(CEILING(X2-42129,0.5),0) but times at noon present problems. Suggestions?

 

[oldbrewer]

have a lookup table that tells you 6/6 is 00:01 to noon and ALL other dates are noon start 24 hours long

row 1 col 1 will be 6/6 00;00 col 2 11:59;59
row 2 col 1 will be 6/6 11:59:59 col 2 6/7 11:59;59 etc

 

[West Man]

Thank you. I was trying to avoid the table and because the problem only existed with noon, I have amended my formula as follows ROUNDDOWN(CEILING(X2-42129,0.5),0)+(MOD(X2,1)=0.5) which appears to have worked.

Thank you again for your time in considering my problem.