Skip to main content

Formula to hide data in a cell if it matches certain data

  • November 11, 2022
  • 5 replies
  • 62 views
D

Is there a wide to hide data in certain cells using a formula?

I am trying to work with date data coming from Memberstack that appears in UNIX form.

  1. I have a column converting the UNIX data to readable data.
DATEADD('1/1/1970',{UNIX Renewal Date from Memberstack},'seconds')

  1. I have a column for Manual renewal dates. Some people are outside the system.

  2. I have a column that merges the two. IF there is a Manual Date, use that, if not, pull the Converted UNIX renewal date. It works mostly fine. It is setting the manual renewal date back a day, which is weird, but not the end of the world.

IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack})
  1. Now the issue is that the UNIX baseline of December, 31, 1969 is showing up anywhere there is a blank cell. Is there a way to get rid of that? Essentially I am looking to either have a blank cell of a simple N/A instead of the 1969 date.

Screenshot attached for reference.

Thanks in advance!

5 replies

K
Forum|alt.badge.img+11
  • KVachon
  • Inspiring
  • November 11, 2022

Hey there!

You should be able to wrap the formula in your Renewal Date FOR… column in another IF formula:

IF(AND({UNIX Renewal Date}='',{Manual Renewal Date} = ''),'',Insert your existing formula here)
D
  • DallasProducers
  • Author
  • New Participant
  • November 12, 2022

Hey there!

You should be able to wrap the formula in your Renewal Date FOR… column in another IF formula:

IF(AND({UNIX Renewal Date}='',{Manual Renewal Date} = ''),'',Insert your existing formula here)

Hi KVachon - Thanks so much for the help! It pulled out all the cells with incorrect data leaving only the renewal dates with actual data.

Now it’s converted back to some sort of crazy date UNIX/EPOCH time format. I tried to use the DATEADD function but it just did the same thing where it added all the 1970 dates.

Any thoughts? I tried the date add both at the beginning and before the second IF function. Neither worked.

K
Forum|alt.badge.img+11
  • KVachon
  • Inspiring
  • November 12, 2022

Hi KVachon - Thanks so much for the help! It pulled out all the cells with incorrect data leaving only the renewal dates with actual data.

Now it’s converted back to some sort of crazy date UNIX/EPOCH time format. I tried to use the DATEADD function but it just did the same thing where it added all the 1970 dates.

Any thoughts? I tried the date add both at the beginning and before the second IF function. Neither worked.


Awesome.

You can use the DATETIME_FORMAT formula to make it to your liking.

D
  • DallasProducers
  • Author
  • New Participant
  • November 12, 2022

Awesome.

You can use the DATETIME_FORMAT formula to make it to your liking.


Ok. So DATE_FORMAT works and get’s me to organize, but it also throws in a whole bunch of errors. I’ve been playing around with IF statements based on some other threads, but I can’t seem to get it to showcase in a way that doesn’t either break the code or revert us back to our previous issue of the 1970 Date.

Here is the code I am using.

DATETIME_FORMAT(
  IF(
    AND({Converted Renewal Date From Memberstack}='',{Manual Renewal Date} = ''),'',
    IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack})),'LL')

K
Forum|alt.badge.img+11
  • KVachon
  • Inspiring
  • November 12, 2022

Ok. So DATE_FORMAT works and get’s me to organize, but it also throws in a whole bunch of errors. I’ve been playing around with IF statements based on some other threads, but I can’t seem to get it to showcase in a way that doesn’t either break the code or revert us back to our previous issue of the 1970 Date.

Here is the code I am using.

DATETIME_FORMAT(
  IF(
    AND({Converted Renewal Date From Memberstack}='',{Manual Renewal Date} = ''),'',
    IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack})),'LL')


Hey there,

You’ll want to use that formula specifically around the date result(s) while still providing the blank (‘’) entry your are defaulting to with no info.

Try the below formula:


IF(
    AND({Converted Renewal Date From Memberstack}='',{Manual Renewal Date} = ''),'',
    DATETIME_FORMAT(IF({Manual Renewal Date}, {Manual Renewal Date}, {Converted Renewal Date From Memberstack}),'LL'))
Terms & ConditionsAccessibility statement