> ## Documentation Index
> Fetch the complete documentation index at: https://docs.equals.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Working with dates

> Tips and tricks on how to analyze dates

Spreadsheet formulas may return different data depending on the format of the cells being analyzed. Here are a few tips and tricks to keep in mind when working with dates in spreadsheets.

### Identifying cell formatting

The first thing you'll want to do when writing a formula based on a date value is to ensure that the cell is storing the data as a date. We have two cells with the same data below, each stored differently:

<img src="https://mintcdn.com/equals/PJ0fZGdeJS9vIpsl/images/docs/f20efad-formatting-paragraph-text.png?fit=max&auto=format&n=PJ0fZGdeJS9vIpsl&q=85&s=f96381087fddcd1f093d00bab1fd45dc" alt="" width="2400" height="1618" data-path="images/docs/f20efad-formatting-paragraph-text.png" />

While at face value both cells appear to display the date April 1st, 2024, only one is storing the date as a date while the other stores it as a text string. A good rule of thumb for identifying this quickly is to see if the cell's content is left-aligned. If so, it is likely a text string. If the cell's content is right-aligned, it's likely stored as a date.

### How formatting will change formula results

Let's say you want to take a sum of the amount paid by your customers for the month of April. If you write a SUMIFS formula that attempts to SUM values that are stored as text (column D) and your criteria (cell I6) is stored as a date, your output will be 0.

<img src="https://mintcdn.com/equals/rY-jt2rKiL-81FoS/images/docs/9f7b6a2-formatting-paragraph-text_3.png?fit=max&auto=format&n=rY-jt2rKiL-81FoS&q=85&s=140933c56ee979b226583e4aa502233c" alt="" width="2400" height="1618" data-path="images/docs/9f7b6a2-formatting-paragraph-text_3.png" />

Notice how this will not happen if you are using the date field as your lookup column (column B).

<img src="https://mintcdn.com/equals/rY-jt2rKiL-81FoS/images/docs/784421b-formatting-paragraph-text_4.png?fit=max&auto=format&n=rY-jt2rKiL-81FoS&q=85&s=ab68136cfe0f320b27a9ce144d509b0c" alt="" width="2400" height="1618" data-path="images/docs/784421b-formatting-paragraph-text_4.png" />

When writing SUMIFS or COUNTIFS statements, we recommend leveraging date values instead of string values.

### Common mistakes

Oftentimes it's tempting to write a TRIM(LEFT...) formula to extract a date value from a timestamp (which is a string value). Instead of leveraging a formula that will return another string value, we'd recommend using =DATE(YEAR(),MONTH(),DAY()) for these cases, or =DATEVALUE(). This will ensure that the output is stored as a serialized date number (ex: April 1, 2024 = 45383) under the hood and will make sure that all formulas evaluate as expected.

***

[Filters and sorts](/docs/filters-and-sorts)

[Date matching formulas](/docs/date-matching-formulas)