# How to round date differences to specific times?

Hi, I would like to calculate the halfway point between two date-time entries, but then I want as a result a time occurring between 8:00 and 20:00. So if the halfway point is outside this timeframe and before 2:00, it would round down to 20:00 the day before, after 2:00, it would round up to 8:00. How would I do this?

Screenshot 2020-11-25 120446.png

What about case "after `20:00`" - should it round down to `20:00` current day ?

Yes, sorry, anything after 20:00 on same day would also round to 20:00

Hello,

while waiting for a response to my comment, see the formula in column `I` of following sample file:
HalftimeByConditions.ods

Formula in column `I` reads (formatted here for better readability of the 4 conditions used)

``````=IFS(       MOD((\$B2+\$C2)/2;1)<2/24                         ;INT((\$B2+\$C2)/2)-4/24;
AND(MOD((\$B2+\$C2)/2;1)>2/24;MOD((\$B2+\$C2)/2;1)<8/24);INT((\$B2+\$C2)/2)+8/24;
MOD((\$B2+\$C2)/2;1)>20/24                        ;INT((\$B2+\$C2)/2)+20/24;
1                                             ;(\$B2+\$C2)/2)
``````

Note(s)

• Columns `D` through `H` are not required for the formula to work (only column `B` and `C` are used). I left em for the sake of understanding, how the formula works and for breakdown/testing purposes.
• If have implemented a condition, which sets >`20:00` to `20:00` (round down).
• Rounding to full hours is in column `J`

Hope that helps.

Awesome! This is exactly what I wanted to calculate. Thank you so much!
{"ft_lang_label":"__label__en","ft_lang_prob":0.97880083,"math_prob":0.9435389,"size":352,"snap":"2023-40-2023-50","text_gpt3_token_len":95,"char_repetition_ratio":0.11781609,"word_repetition_ratio":0.0,"special_character_ratio":0.28977272,"punctuation_ratio":0.16853933,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9901359,"pos_list":[0,1,2],"im_url_duplicate_count":[null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-09-23T20:18:01Z\",\"WARC-Record-ID\":\"<urn:uuid:485fe6b2-48c8-41bb-a143-3ccd617491ea>\",\"Content-Length\":\"23495\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:fb252d70-d8b8-404a-9ad7-32f254c05720>\",\"WARC-Concurrent-To\":\"<urn:uuid:a1e2ed5a-a8a2-424c-9af8-0fbba7053815>\",\"WARC-IP-Address\":\"65.109.107.234\",\"WARC-Target-URI\":\"https://ask.libreoffice.org/t/how-to-round-date-differences-to-specific-times/58995\",\"WARC-Payload-Digest\":\"sha1:CMYDL7GVXRM3N3DKCAFCDUFLO2SHAOLD\",\"WARC-Block-Digest\":\"sha1:53SCXJLQRJ7BZLEB5XFLSUAC6FE26UC3\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-40/CC-MAIN-2023-40_segments_1695233506528.3_warc_CC-MAIN-20230923194908-20230923224908-00737.warc.gz\"}"}