Today / Yesterday formulas

megan.griffiths

Hi all,

I need to create a dashboard to include all calls made to clients, either today or yesterday (the idea being that the charts would change on a daily basis). My issue is that I don't think I can use the Today or Today -1 type formulas as the date column is NOT a date column type, but a text column type. I can't change it to a date column as it is auto populated using an API link from another site, so I cant change this as it will mess us the API link.

All dates are in the same format: Monday, October 19, 2020

Can anyone help with what formula I could use, or is this not possible?

Thanks in advance 😀

Meg

David Tutwiler

I wonder if it is an issue with the format of the dates being different. If you take TODAY out and put in DATE(2020, 10, 21), does it have the same issue? What about if you wrap your date value in a DATE() function? Like DATE({Last Call Date})

David Tutwiler

I actually have this broken down into multiple formulas to make it easier to follow, but you could theoretically combine them into an ugly massive formula.

Getting the year is easy. Just pull the right 4 characters. Something like:

=RIGHT(Date@row, 4)

To get the Month, you'll need to get a bit more creative. To strip the month out of the text you could use:

=MID(Date@row, FIND(",", Date@row) + 2, FIND(",", Date@row, 10) - FIND(",", Date@row) - 5)

^This formula uses the commas as a guide and strips out the month wherever it is in the text. You'll need another nested IF statement that converts the month name to a date. Something like:

=IF(Month@row = "January", 1, IF(Month@row = "February", 2, etc,etc

To get the date you can use:

=MID(Date@row, FIND(",", Date@row, 10) - 2, 2)

^ Again this just uses the commas as a guide and strips the date out.

Now, once we have all of our elements, you can put the numbers into the DATE formula like this:

=DATE(VALUE(Year@row), VALUE(Month@row), VALUE(Day@row))

Each element is wrapped in a Value function because the formulas we used above return a type String instead of a number.

That would give you the current date and would allow you to use TODAY and TODAY(-1).

Sorry, a bit long winded, but I think that will work.

megan.griffiths

Thanks David. I have managed to change the field to a date field, however the following formula is still not working - can you please advise?

The idea being, if the adviser name, is John Smith, CCA Name is JS and the appt was booked today from the Last Call Date field, it would count:

=COUNTIFS({Adviser Name}, "John Smith", {CCA Name}, "JS", {Last Call Date}, TODAY())

I will also need one for Yesterdays date, but the TODAY () -1 is also not working...

Any help would be appreciated

Thanks,

Meg

David Tutwiler

The logic of the statement looks right. The COUNTIFS works as an AND function. So it's looking for the Adviser Name is John Smith AND that the CCA Name is JS AND that the Last Call Date was Today's date.

If that still isn't working, are the reference ranges just the single column that has the data in it, or does it include multiple columns?

megan.griffiths

Hi David, I have tried everything! The formula works as expected without the TODAY function, but as soon as I add that in, it breaks...

I am pulling it from a different sheet, so I have just renamed the tag to make it easier to read. Its multiple columns but from the same sheet...

David Tutwiler

I wonder if it is an issue with the format of the dates being different. If you take TODAY out and put in DATE(2020, 10, 21), does it have the same issue? What about if you wrap your date value in a DATE() function? Like DATE({Last Call Date})