SOLVED

Formula to only pull a number if a date is reached

Copper Contributor

I need help please with the below formula:
The move-in date on the database is Jul-23, so I need a formula that says "if the date on database is x, then pull the number on column y, otherwise leave blank.
The problem is the IF formula does recognize dates, only text...Also as soon as we get a number, the next column will be equal to that number (so from Jul-23 till Nov-23 we would have the same number) Can you help me?

AnaDeAlmeida_0-1657049974364.png

 

1 Reply
best response confirmed by AnaDeAlmeida (Copper Contributor)
Solution

@AnaDeAlmeida 

IF will recognise texts, numbers and booleans. As long as you "feed" it correctly. For instance, dates in Excel are sequential numbers, where the first day is January 1, 1900. Today (July 6, 2022), we are on day number 44748, and you can format it in many different (custom) formats, like mmm-yy

 

When you write an IF formula like:

=IF(A1="Jul-23", "x","y") and A1 contains a real data, it will alway return the "y" (value_if_false) as a real date (thus a number) will never match the text "Jul-23".

 

Let's say A1 on contains todays date, July 6, 2022 (as a real date!). Then you could write something like:

=IF(A1<= DATE(2022,7,31),"x","y") resulting in "x".

A1 is a real data and DATE also creates a real date. So, now you can compare them and Excel will return the true or false value correctly.

 

Furthermore, you need to look into the use of EOMONTH and/or EDATE, combined with custom formats for building your Month headers in the dark green area of your screenshot. Upload or share a file if you can't figure it out.

 

 

 

 

1 best response

Accepted Solutions
best response confirmed by AnaDeAlmeida (Copper Contributor)
Solution

@AnaDeAlmeida 

IF will recognise texts, numbers and booleans. As long as you "feed" it correctly. For instance, dates in Excel are sequential numbers, where the first day is January 1, 1900. Today (July 6, 2022), we are on day number 44748, and you can format it in many different (custom) formats, like mmm-yy

 

When you write an IF formula like:

=IF(A1="Jul-23", "x","y") and A1 contains a real data, it will alway return the "y" (value_if_false) as a real date (thus a number) will never match the text "Jul-23".

 

Let's say A1 on contains todays date, July 6, 2022 (as a real date!). Then you could write something like:

=IF(A1<= DATE(2022,7,31),"x","y") resulting in "x".

A1 is a real data and DATE also creates a real date. So, now you can compare them and Excel will return the true or false value correctly.

 

Furthermore, you need to look into the use of EOMONTH and/or EDATE, combined with custom formats for building your Month headers in the dark green area of your screenshot. Upload or share a file if you can't figure it out.

 

 

 

 

View solution in original post