IF function

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
Please help me to solve this if function.

Cell A contains <TALLYMESSAGE xmlns:UDF="TallyUDF"><VOUCHER><OLDAUDITENTRYIDS.LIST TYPE="Number"><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>

Cell B contains TEXT('Sheet1'!A2,"yyyymmdd") from another sheet

If cell B=19000100, show blank, else show <TALLYMESSAGE xmlns:UDF="TallyUDF"><VOUCHER><OLDAUDITENTRYIDS.LIST TYPE="Number"><OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS></OLDAUDITENTRYIDS.LIST>

B column contains different text value of date in yyyymmdd format. 20200401, 20200402,etc, The cell which contains 19000100, should display a blank cell in A column.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GraH

Active Member
Joined
Mar 22, 2020
Messages
473
Office Version
365, 2016
Platform
Windows
Maybe it is = IF ( B2 = 0 , "", A2)
 

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
Maybe it is = IF ( B2 = 0 , "", A2)
No. The cell is not blank. B2 contains a formula from the previous sheet which is dragged to the end and will display a yyyymmdd format. If in the previous sheet if the date cell is blank it will show 19000100.
 

GraH

Active Member
Joined
Mar 22, 2020
Messages
473
Office Version
365, 2016
Platform
Windows
So 19000100 in the format yyyymmdd reads as the 0 of jan 1900. That's a zero value, hence my formula (no saying B2 is blank, but zero). Format as number and see if I'm wrong.
 

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
So 19000100 in the format yyyymmdd reads as the 0 of jan 1900. That's a zero value, hence my formula (no saying B2 is blank, but zero). Format as number and see if I'm wrong.
 
Last edited:

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
Thanks. It worked.
 
Last edited:

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
😥
 

GraH

Active Member
Joined
Mar 22, 2020
Messages
473
Office Version
365, 2016
Platform
Windows
Can you upload a data sample using XL2BB add-on. Or paste a range of cells with example where it is not working. Do include the range of dates on the other sheet as well.

Guessing for the moment...
 

RAJESH1960

New Member
Joined
Mar 26, 2020
Messages
36
Office Version
2019
Platform
Windows
I am trying to know how to use XL2BB but not understanding it. Can I send you an image instead.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,057
Messages
5,466,323
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top