Nothing’s more frustrating than spending time formatting dates in Google Sheets only to have them display incorrectly, break your charts, or cause formulas to fail. If you’re dealing with Google Sheets date format not working issues, you’re not alone—date formatting problems are among the most common spreadsheet headaches.
Whether your dates are showing as numbers, your custom formats aren’t applying, or your MONTH() function isn’t recognizing dd/mm/yyyy formats, this guide will walk you through every solution. We’ll cover the most common causes and provide step-by-step fixes for each scenario.
Why Google Sheets Date Formats Fail
The Root Cause: Text vs Date Values
The number one reason date format not working in Google Sheets occurs is that your “dates” are actually stored as text, not date values. Google Sheets can only apply date formatting to cells it recognizes as actual dates.
When you import data from other sources, copy-paste from websites, or manually enter dates in non-standard formats, Google Sheets often treats these entries as text strings rather than date values.
Regional Settings and Locale Issues
Google Sheets uses your account’s locale settings to interpret date formats. If your data uses dd/mm/yyyy format but your account is set to US locale (mm/dd/yyyy), Google Sheets may not recognize your dates correctly.
Mixed Data Types in Columns
Having a mix of properly formatted dates and text strings in the same column can cause formatting issues and break functions that expect consistent date values.
Quick Diagnosis: Is Your Date Actually a Date?
Before diving into solutions, let’s identify what type of data you’re working with:
Test 1: Check Cell Alignment
- Real dates: Automatically align to the right in cells
- Text dates: Align to the left by default
Test 2: Try Basic Math
- Select a cell with your “date”
- Type
=A1+1(replacing A1 with your cell reference) - If it returns a date one day later: Your data is a proper date
- If it returns an error: Your data is text
Test 3: Format Menu Response
- Select your date cell
- Go to Format > Number > Date
- If formatting applies: You have a real date
- If nothing changes: You have text that looks like a date
Fix 1: Converting Text Dates to Proper Dates
Method 1: Using DATEVALUE Function
If your dates are stored as text, use the DATEVALUE function to convert them
This works for most standard date text formats and will convert them to proper date values that can be formatted.
Method 2: Text to Columns Conversion
For entire columns of text dates:
- Select your date column
- Go to Data > Split text to columns
- Choose “Custom” separator
- Leave the separator field empty and click “Split”
- Google Sheets will attempt to auto-detect and convert date formats
Method 3: Manual Conversion for dd/mm/yyyy
If you have dd/mm/yyyy text dates that aren’t being recognized:
This formula extracts day, month, and year from a dd/mm/yyyy text string and creates a proper date.
Fix 2: Custom Date Format Not Working Solutions
Google Sheets custom date format not working often happens when the underlying data isn’t recognized as dates. Here’s how to fix it:
Step 1: Ensure Data is Date Format First
Before applying custom formatting, make sure your data is recognized as dates using the methods above.
Step 2: Apply Custom Format Correctly
- Select your date cells
- Go to Format > Number > More formats > More date and time formats
- Create your custom format using these codes:
dd= day with leading zero (01, 02, etc.)d= day without leading zero (1, 2, etc.)mm= month with leading zero (01, 02, etc.)m= month without leading zero (1, 2, etc.)yyyy= four-digit yearyy= two-digit year
Step 3: Common Custom Format Examples
dd/mm/yyyyfor 25/12/2024mm-dd-yyyyfor 12-25-2024dd mmm yyyyfor 25 Dec 2024dddd, mmmm dd, yyyyfor Wednesday, December 25, 2024
Fix 3: Date Format Not Working with Weekdays
Google Sheets date format not working with weekday issues usually stem from trying to format text as dates or using incorrect format codes.
Solution for Weekday Display Issues
If your weekdays aren’t showing correctly:
- Verify your data is in date format (not text)
- Use proper weekday format codes:
dddd= full weekday name (Wednesday)ddd= abbreviated weekday (Wed)d= weekday number (4 for Wednesday)
Formula Alternative for Weekdays
If formatting isn’t working, use the WEEKDAY function:
This converts any proper date to its weekday name.
Common Weekday Formula Solutions
Fix 4: Date Format Issues in Charts
Google Sheets date format not working for chart problems typically occur when the chart doesn’t recognize your date column as time-series data.
Solution Steps for Chart Date Issues
Step 1: Fix Source Data
Ensure your date column contains proper date values (not text) before creating the chart.
Step 2: Chart Setup
- Select your data range including dates
- Insert > Chart
- In Chart Editor, go to Setup tab
- Set X-axis to your date column
- Chart type should automatically detect time-series data
Step 3: Format Chart Date Axis
- In Chart Editor, go to Customize tab
- Select “Horizontal axis”
- Choose your preferred date format from the Format dropdown
Step 4: Manual Chart Date Formatting
If automatic detection fails:
- Double-click your chart
- Chart Editor > Customize > Horizontal axis
- Under “Format,” select “Date”
- Choose your preferred date display format
Fix 5: MONTH() Function Not Working with dd/mm/yyyy
The issue month() not working for date format of dd/mm/yyyy google sheets typically occurs when your dd/mm/yyyy data is stored as text rather than proper dates.
Solution 1: Convert Text to Date First
If your dd/mm/yyyy data is text:
Solution 2: Extract Month from dd/mm/yyyy Text
For text in dd/mm/yyyy format:
This extracts characters 4-5 (the month) and converts them to a number.
Solution 3: Comprehensive dd/mm/yyyy Conversion
Create a proper date first, then use MONTH():
Alternative MONTH() Formulas for Different Formats
Prevention: Best Practices for Date Management
Import Data Correctly
When importing from CSV or other sources:
- Use File > Import
- Select “Convert text to numbers, dates, and formulas”
- Review the preview to ensure dates are recognized correctly
Standardize Date Entry
Establish consistent date entry formats across your team:
- Use YYYY-MM-DD format for universal recognition
- Or ensure everyone uses the same regional format
- Consider data validation rules to enforce consistent formats
Regional Settings Management
Check your Google Sheets locale:
- File > Settings
- Review “Locale” and “Time zone” settings
- Adjust if they don’t match your data format expectations
Advanced Troubleshooting Techniques
Using ISDATE Function to Test
Create a helper column to identify which cells contain proper dates:
Batch Conversion Formula
To convert an entire column of text dates:
Handling Mixed Date Formats
For columns with mixed dd/mm/yyyy and mm/dd/yyyy formats:
When to Consider Alternative Solutions
If you’re frequently struggling with date formatting issues in Google Sheets, it might be time to consider more robust data management solutions. Platforms like Noloco offer built-in date handling that automatically manages formatting, validation, and display without the manual troubleshooting required in spreadsheets.
For businesses managing complex data workflows, dedicated database solutions can eliminate these formatting headaches while providing better data integrity and user experience.
Conclusion
Google Sheets date format not working issues are frustrating but almost always solvable. The key is identifying whether you’re working with proper date values or text that looks like dates, then applying the appropriate conversion and formatting techniques.
Remember these key troubleshooting steps:
- Test if your data is actually recognized as dates
- Convert text to proper date values when necessary
- Apply formatting only after ensuring proper date values
- Use locale-appropriate formats for your data
- Consider helper formulas for complex conversion needs
With these solutions, you should be able to resolve most date formatting issues and get your spreadsheets working smoothly. For more advanced data management needs, explore our other guides on building robust business applications that handle data formatting automatically.
Quick Reference: Common Date Formulas
Keep this reference handy for quick solutions to common date format not working in Google Sheets scenarios
Written by
Gill O'Leary
Head of Partnerships at Noloco
Gill is Head of Partnerships at Noloco, where she’s building a thriving partner ecosystem to help businesses unlock the power of no-code and drive digital transformation. With a career spanning product, solutions engineering, and partnerships, she’s worked with both fast-moving startups and global tech leaders. Gill is passionate about making technology accessible to everyone—especially those without a technical background. On the blog, she writes about strategic partnerships, the evolving no-code landscape, and how businesses can leverage AI and automation to scale smarter.