Hello i know that i have already asked the same question, but i am currently not getting the desired results.

So i have a spreadsheet with some values in A cells like this :

  • 1:30
  • 0:20
  • -08:10:00

Now what i want is if the cell contains the “-” then in the B cell return the -08 and in C cell the 10 and if it doesn contains it the return positive
Here’s a pic of what i would like to look like this :

image description

You didn’t get the desired answer in SPLIT CELLS (HOURS) because you didn’t ask the right question…

i know i didn’t asked it and that’s my fault. Sorry

I find it odd that you want a negative -8 and a positive 10 in your example, but here is what you’re asking for:

  • B1: =IF(A1>=0;"positive";CEILING(A1*24))
  • C1: =IF(A1>=0;"positive";(CEILING(A1*24)-A1*24)*60)

Then select cells B1:C1, copy to clipboard, select cells B2:C3, paste.