@Rasha I think I got it. I feel pretty good about this one. This formula will calculate the time to the next half hour. It does float between 0 and 1 seconds after the minute, but appears to stick on the exact minute of the next half hour and switching to not show seconds will give you a good view. I did watch the time switch from 5:59 to 6:00 and after that, the value changed from 6:00 to 6:30, so I feel pretty good about it.
The kicker here is that this will require a seed date. This will need to be a column with a date/time. The date and hour can be anything, but the minutes and seconds need to be on the hour, so equal to 0. For example, this is what I have as a seed date/time.
This is the complete math formula:
X+
(
CEILING(((X-Seed)-ROUND(X-Seed))/.0208333)
-
FLOOR(((X-Seed)-ROUND(X-Seed))/.0208333*1000)/1000
)
/48
Hope it works for you.
Edit: This may be better and just a hair more accurate. Basically the same either way.
X+
(
CEILING(((X-Seed)-ROUND(X-Seed))*48)
-
FLOOR(((X-Seed)-ROUND(X-Seed))*48*1000)/1000
)
/48