Subtracting a field containing both a time and a date on LibreOffice Calc

[cynicalthelizardseagull]

Howdy,

As per the getting started guide and just in case the OS being used is Windows 10, LibreOffice version 7.0.0.3 64-bit and file format is ods.

I can’t for the life of me figure this out after reading countless related questions here.

So my problem is as follows:
I would like to subtract a field in the format of “HH:MM DD/MM” say, “13:04 26/07” subtracted by 32 hours so on the field below (or wherever) it’d look something like “05:04 25/07”.

I’ve been trying with -TIMEVALUEs and -DATEVALUEs but my knowledge of all the possible functions are really limited since reading all the other posts was my introduction to any of these time and date functions and I’m having a real tough time comprehending them so I don’t know if I’m headed entirely in the wrong direction.

So far I only managed to subtract from individual fields of “HH:MM” and “DD/MM”. I imagine that trying to play with the bizarre number of 32 hours is not doing me any favors when I want to cram both time and a date in one box.

Thank you.

[Wanderer]

Usually calc use a day-number since 1900 as integer part of thr cell and adds the fraction of hours after the decimal . (or , depending on your local config).

So if your first cell is already a Date, all you need to do is converting the hours to the same fraction of the day:=32/24 where 24 is the typical number of hours on this planet (locale may differ e.g. on Mars)
Simple cases like this can be checked directly:
32 = 24 + 8 = 1.33333 days

So if your aforement field is F1 use

=F1 - (32/24) 

and check the result.

But if your cell contains only text you have to convert to date first.

[cynicalthelizardseagull]

Oh wow thank you so much! Took me longer than I’d like to admit to realize I was being stubborn and that all I had to do was put the date first and it didn’t even occur to me that I can just calculate the date and time like that, thanks doubly!