Bird
Raised Fist0
Google Sheetsspreadsheet~15 mins

DATE function in Google Sheets - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a sales analyst at a retail company.
📋 Request: Your manager wants a report showing the exact date of each sale using separate year, month, and day columns.
📊 Data: You have a table with sales data including separate columns for Year, Month, Day, and Sales Amount.
🎯 Deliverable: Create a new column that combines Year, Month, and Day into a proper date using the DATE function.
Progress0 / 4 steps
Sample Data
YearMonthDaySales Amount
2024115250
202423300
2024228450
2024310500
202445600
2024420700
202451800
2024515900
1
Step 1: Insert a new column next to the Day column and label it 'Sale Date'.
Expected Result
A new empty column named 'Sale Date' appears next to the Day column.
2
Step 2: In the first cell under 'Sale Date' (e.g., E2), enter the formula to combine Year, Month, and Day into a date.
=DATE(A2, B2, C2)
Expected Result
The cell shows the date 1/15/2024 formatted as a date.
3
Step 3: Copy the formula down the 'Sale Date' column for all rows.
Drag the fill handle from E2 down to E9.
Expected Result
All rows show the correct date corresponding to their Year, Month, and Day values.
4
Step 4: Format the 'Sale Date' column to display dates in 'MM/DD/YYYY' format for clarity.
Select the 'Sale Date' column, then Format > Number > Date.
Expected Result
Dates appear in the format like 01/15/2024, 02/03/2024, etc.
Final Result
Year | Month | Day | Sales Amount | Sale Date
---------------------------------------------
2024 | 1     | 15  | 250          | 01/15/2024
2024 | 2     | 3   | 300          | 02/03/2024
2024 | 2     | 28  | 450          | 02/28/2024
2024 | 3     | 10  | 500          | 03/10/2024
2024 | 4     | 5   | 600          | 04/05/2024
2024 | 4     | 20  | 700          | 04/20/2024
2024 | 5     | 1   | 800          | 05/01/2024
2024 | 5     | 15  | 900          | 05/15/2024
✓The DATE function successfully combines separate year, month, and day values into a proper date.
✓This makes it easier to sort, filter, and analyze sales data by exact dates.
✓Formatting the date column improves readability for reports.
Bonus Challenge

Create a new column that shows the weekday name (e.g., Monday, Tuesday) for each sale date.

Show Hint
Use the TEXT function with the format "dddd" on the Sale Date column, like =TEXT(E2, "dddd").

Practice

(1/5)
1. What does the DATE function do in Google Sheets?
easy
A. It creates a date from year, month, and day numbers.
B. It calculates the difference between two dates.
C. It converts text to uppercase.
D. It adds numbers together.

Solution

  1. Step 1: Understand the purpose of DATE

    The DATE function takes three numbers: year, month, and day, and combines them into a valid date.

  2. Step 2: Compare with other options

    Other options describe different functions like date difference or text conversion, which are not what DATE does.

  3. Final Answer:

    It creates a date from year, month, and day numbers. -> Option A

  4. Quick Check:

    DATE function = create date [OK]
Hint: DATE joins year, month, day into one date [OK]
Common Mistakes:
  • Confusing DATE with functions like TODAY or NOW
  • Thinking DATE calculates differences
  • Assuming DATE formats text
2. Which of these is the correct syntax for the DATE function in Google Sheets?
easy
A. DATE(month, day, year)
B. DATE(day, month, year)
C. DATE(year, month, day)
D. DATE(year-day-month)

Solution

  1. Step 1: Recall DATE function syntax

    The correct order is year first, then month, then day: DATE(year, month, day).

  2. Step 2: Check other options

    Options B and C have wrong order; D uses invalid syntax with dashes and no commas.

  3. Final Answer:

    DATE(year, month, day) -> Option C

  4. Quick Check:

    Correct syntax = DATE(year, month, day) [OK]
Hint: Remember: year, month, day order in DATE [OK]
Common Mistakes:
  • Swapping day and month positions
  • Using dashes instead of commas
  • Putting month first
3. What is the result of =DATE(2024, 13, 1) in Google Sheets?
medium
A. December 1, 2024
B. January 1, 2025
C. January 1, 2024
D. Error

Solution

  1. Step 1: Understand month overflow in DATE

    The month value 13 means 1 month after December, so it moves to January of next year.

  2. Step 2: Calculate the adjusted date

    So DATE(2024, 13, 1) becomes January 1, 2025.

  3. Final Answer:

    January 1, 2025 -> Option B

  4. Quick Check:

    Month 13 = January next year [OK]
Hint: Months >12 roll over to next year automatically [OK]
Common Mistakes:
  • Expecting error for month 13
  • Thinking month 13 means December
  • Ignoring automatic date adjustment
4. You entered =DATE(2023, 2, 30) but it shows March 2, 2023. Why?
medium
A. February has only 28 or 29 days, so day 30 rolls into March.
B. DATE function has a bug showing wrong dates.
C. You must enter day before month in DATE.
D. Google Sheets does not support dates in February.

Solution

  1. Step 1: Check day overflow behavior

    February usually has 28 or 29 days, so day 30 is invalid and rolls over to March 2.

  2. Step 2: Understand DATE auto-adjustment

    DATE automatically adjusts invalid days by adding extra days to next month.

  3. Final Answer:

    February has only 28 or 29 days, so day 30 rolls into March. -> Option A

  4. Quick Check:

    Invalid day rolls over to next month [OK]
Hint: Invalid days push date into next month automatically [OK]
Common Mistakes:
  • Thinking DATE has bugs
  • Swapping day and month order
  • Believing February dates are unsupported
5. You have year in A1=2023, month in B1=11, and day in C1=35. Which formula correctly gives the valid date?
hard
A. =DATE(A1, B1+1, C1-31)
B. =DATE(A1, B1, 30)
C. =DATE(A1, 12, C1)
D. =DATE(A1, B1, C1)

Solution

  1. Step 1: Understand how DATE handles overflow

    DATE automatically adjusts day 35 in November to December 5 (35 - 30 = 5 days into next month).

  2. Step 2: Check each option

    =DATE(A1, B1, C1) uses original values and lets DATE fix the date correctly. Others manually change month or day, which is unnecessary or incorrect.

  3. Final Answer:

    =DATE(A1, B1, C1) -> Option D

  4. Quick Check:

    DATE auto-fixes overflow days [OK]
Hint: Use DATE with raw values; it fixes overflow automatically [OK]
Common Mistakes:
  • Manually adjusting month or day before DATE
  • Assuming DATE errors on large day numbers
  • Changing month without reason
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard