Create Date-Based Formulas

What You Will Learn

You will learn how to ask Gemini for date-based formulas that create month, week, fiscal quarter, tenure, aging, and cohort fields for business analysis.

Required Dataset

No external dataset is required.

Create two tabs:

Date_Formula_Practice

Date_Check

Before You Start

In Date_Formula_Practice, create sample columns for:

Record ID

Customer Name

Signup Date

Invoice Date

Due Date

Employee Start Date

Amount

Add 10–15 sample rows. Make sure the date columns are formatted as real dates, not text.

Step-by-Step Instructions

Step 1. Ask Gemini for date formulas

Open Gemini and enter:

Act as a Google Sheets formula coach.

Create date-based formulas for the Date_Formula_Practice tab.

I need formulas for:

1. Month from Invoice Date
2. Week start date from Invoice Date
3. Calendar quarter from Invoice Date
4. Fiscal quarter assuming fiscal year starts in April
5. Aging days from Due Date
6. Employee tenure in years from Employee Start Date
7. Customer signup cohort month from Signup Date

Return formulas, explanations, and validation checks.

Step 2. Add common formulas

For invoice month:

=TEXT(D2,”yyyy-mm”)

For week start date:

=D2-WEEKDAY(D2,2)+1

For calendar quarter:

=”Q”&ROUNDUP(MONTH(D2)/3,0)

For fiscal quarter with fiscal year starting in April:

=”FQ”&ROUNDUP(MOD(MONTH(D2)-4,12)+1,3)/3

For aging days from due date:

=TODAY()-E2

For tenure in years:

=DATEDIF(F2,TODAY(),”Y”)

For signup cohort month:

=TEXT(C2,”yyyy-mm”)

Copy formulas down.

Step 3. Add review logic

In a Review Needed column, use:

=IF(OR(C2=””,D2=””,E2=””,F2=””),”Yes”,”No”)

This flags rows with missing key dates.

Expected Result

You should have date-based fields that make the dataset easier to analyze by month, week, quarter, fiscal quarter, aging, tenure, and cohort.

Check Your Work

Confirm that dates are formatted correctly, week start dates fall on Mondays, fiscal quarters follow the April start rule, aging days make sense, and tenure updates based on today’s date.

Optional Extension

Ask Gemini:

Review my date formulas. Identify any issues with fiscal quarter logic, text-formatted dates, missing dates, negative aging, or formulas that change because they use TODAY().