Is it possible to have Libreoffice Calc display times in 36-hour format, i.e. 8am the next day would be shown as 32:00 instead of 8:00?
How do you define “next day”?
What yesterday was tomorrow will tomorrow be yesterday.
You need to unambiguously make specify the meaning of “this day” probably.
The day is defined in a different column as a string including any of the values 1-7 as required.
Whether numeric or given as a as text, a “point in time” time can either be given as a
-1- full-grown date-time stamp (including the calendaric day somehow) within an (in principle) infinite range, but probably only showing the time part or
-2- TimeOfDay only, and then ambiguous concerning the day (made look “cyclic”).
Concerning the question, the second would require an explicit addendum distinguishing the day like “current” or “next” (or probably “out of range”).
The first alternative would require a specification of the “current day” with respect to every time-stamp, and also a clarification about what should happen if the actual value is outside the 36-hour-range.
I would need to have a reduced example of your data as an .ods.. Though reduced, it should not just contain three examples you currently think of, but examples of “everything that can occasioanlly happen”.
Quick and dirty solution:
- Use brackets around the hour part of the format string to accumulate beyond 24 hours:
[HH]:MM:SS - 36h is 3/2 days, so to have the time wrap to zero every 36 hours:
MOD(< time value > ; 3 / 2 ) - To count “day-and-a-half” time segments (when time accumulates beyond 36 hours):
INT( < time value > * 2 / 3 )
Note: < time value > is to be replaced by the actual time data (cell address or name).
If you explain what you need this for, you may get a better suggestion.
Thanks for that. The reason I need it is I have a list of 36-hour times formatted as text which I need to be able to sort, as well as calculate differences between values etc. Datevalue() converts to 24hr clock but everything above 24hrs gets converted to 00-12 instead of 24-36.
If that is all you need, you should be good with only the first bullet point concerning format.
… everything above 24hrs gets converted to 00-12 instead of 24-36.
So, if you use the suggested format code above, does it fix the displayed time?
Yes, the square brackets works for detection. Thanks 
The Mod function is still returning “00:10” instead of “24:10”, and it keeps replacing the ; with , - could that be the reason, and if so how do I force it to stay as a semicolon?
Don’t blame the MOD(). The change to the separator is not important. As long as you only work with input numbers < 36h and never accumulate them, you don’t need it anyway.
The culprit may be the TIMEVALUE() function if you are using that. It will return time of day (wraps at 24 hours). Use plain and simple VALUE() instead.
The DATEVALUE() you mentioned will return the date/days part of your data, and should not yield the time value you indicated.
Yeah, I had Mod(Datevalue()… instead of just Mod, my mistake. Appears to be working fine now