Ask Your Question


asked 2019-02-20 20:48:16 +0100

kik94 gravatar image

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

edit retag flag offensive close merge delete



You didn't get the desired answer in because you didn't ask the right question..

erAck gravatar imageerAck ( 2019-02-20 21:01:05 +0100 )edit

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

kik94 gravatar imagekik94 ( 2019-02-20 21:05:03 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-02-20 21:23:01 +0100

erAck gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-20 20:48:16 +0100

Seen: 35 times

Last updated: Feb 20