How can I assign a 0 for all negative values, and 1 for all positive values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2439807%22%20slang%3D%22en-US%22%3EHow%20can%20I%20assign%20a%200%20for%20all%20negative%20values%2C%20and%201%20for%20all%20positive%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439807%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20I'm%20trying%20to%20figure%20out%20a%20way%20to%20assign%20a%200%20for%20all%20negative%20values%2Ftimes%20in%20a%20column%2C%20and%20a%201%20for%20all%20positive%20values%2Ftimes%20in%20the%20same%20column.%20I%20know%20that%20I%20could%20somehow%20do%20it%20with%20an%20IF%20function%2C%20but%20I%20don't%20know%20how%20to%20write%20the%20logic%20test%20for%20that.%20Any%20help%20is%20appreciated!%20Here's%20a%20sample%20of%20what%20I%20need%20to%20make%20happen%20on%20the%20bottom%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20(Original)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20B%20(What%20I%20need)%3C%2FP%3E%3CP%3E67%3A20%3A19%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3E-22%3A30%3A12%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B0%3C%2FP%3E%3CP%3E00%3A01%3A00%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3E-00%3A00%3A01%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B0%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2439807%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2439893%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20assign%20a%200%20for%20all%20negative%20values%2C%20and%201%20for%20all%20positive%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076847%22%20target%3D%22_blank%22%3E%40andyhe74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(0%2CSIGN(A1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440088%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20assign%20a%200%20for%20all%20negative%20values%2C%20and%201%20for%20all%20positive%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1076847%22%20target%3D%22_blank%22%3E%40andyhe74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20keep%20negative%20time%3F%20Most%20probably%20there%20are%20texts%20in%20column%20A.%20In%20general%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--(LEFT(A1)%26lt%3B%26gt%3B%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eshall%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello all, I'm trying to figure out a way to assign a 0 for all negative values/times in a column, and a 1 for all positive values/times in the same column. I know that I could somehow do it with an IF function, but I don't know how to write the logic test for that. Any help is appreciated! Here's a sample of what I need to make happen on the bottom:

 

Column A (Original)                        Column B (What I need)

67:20:19                                              1

-22:30:12                                             0

00:01:00                                              1

-00:00:01                                             0

2 Replies

@andyhe74 

How do you keep negative time? Most probably there are texts in column A. In general

=--(LEFT(A1)<>"-")

shall work.