How do I use the "Standardize Date Formats Across Files" prompt?

Copy the prompt template, fill in the variables (FILE_TYPES, CURRENT_FORMATS, SAMPLE_DATA, TARGET_FORMAT, RECORD_COUNT), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this data prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always backup your original data before running any transformation processes Test your standardization rules on a small sample first to identify edge cases Create a separate column for the standardized dates to compare against originals Document any assumptions made about ambiguous formats (MM/DD vs DD/MM) for future reference Use data validation tools to verify the standardized dates fall within expected ranges

data intermediate

Standardize Date Formats Across Files

AI prompt to clean and standardize inconsistent date formats across multiple files. Perfect for data analysis and database imports.

Works with: chatgptclaudegemini

Prompt Template

I need to standardize date formats across multiple data files. Please analyze the provided date samples and create a comprehensive data cleaning strategy. **Source Data Information:** - File types: [FILE_TYPES] - Current date formats found: [CURRENT_FORMATS] - Sample data entries: [SAMPLE_DATA] - Target date format: [TARGET_FORMAT] - Total number of records: [RECORD_COUNT] **Requirements:** 1. Identify all unique date format patterns in the sample data 2. Create transformation rules for each pattern to convert to the target format 3. Flag any ambiguous dates that need manual review (e.g., MM/DD vs DD/MM confusion) 4. Provide regex patterns or formulas for common spreadsheet applications 5. Generate validation rules to verify the transformation accuracy 6. Create error handling procedures for invalid or unparseable dates **Please provide:** - A detailed mapping table showing: Original Format → Target Format → Transformation Rule - Step-by-step instructions for implementing the standardization - Code snippets or formulas for automated processing - Quality assurance checklist to verify results - Recommendations for handling edge cases and invalid dates Ensure the solution maintains data integrity while maximizing successful conversions.

Variables to Customize

[FILE_TYPES]

Types of files containing the date data

Example: CSV files, Excel spreadsheets, JSON exports

[CURRENT_FORMATS]

List of current date formats found in your data

Example: MM/DD/YYYY, DD-MM-YY, YYYY.MM.DD, Month DD, YYYY

[SAMPLE_DATA]

Representative examples of actual date entries from your files

Example: 03/15/2023, 15-03-23, 2023.03.15, March 15, 2023, 3/15/23

[TARGET_FORMAT]

Your desired standardized date format

Example: YYYY-MM-DD

[RECORD_COUNT]

Approximate number of records to be processed

Example: 50,000 records across 12 files

Example Output

**Date Format Standardization Analysis** **Identified Patterns:** 1. MM/DD/YYYY → YYYY-MM-DD (e.g., 03/15/2023 → 2023-03-15) 2. DD-MM-YY → YYYY-MM-DD (e.g., 15-03-23 → 2023-03-15) 3. YYYY.MM.DD → YYYY-MM-DD (e.g., 2023.03.15 → 2023-03-15) 4. Month DD, YYYY → YYYY-MM-DD (e.g., March 15, 2023 → 2023-03-15) **Transformation Rules:** - Pattern 1: Split by '/', rearrange as YYYY-MM-DD with zero-padding - Pattern 2: Split by '-', assume 20XX for YY, rearrange as YYYY-MM-DD - Pattern 3: Replace '.' with '-' - Pattern 4: Parse month name to number, rearrange components **Excel Formula Examples:** ``` =TEXT(DATEVALUE(A1),"YYYY-MM-DD") =IF(LEN(A1)=8,TEXT(DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2)),"YYYY-MM-DD"),"ERROR") ``` **Validation Rules:** - Verify all output dates match YYYY-MM-DD pattern - Check date ranges are realistic (1900-2030) - Flag conversion failures for manual review **Quality Assurance Steps:** 1. Test transformations on sample data subset 2. Validate month values (01-12) and day values (01-31) 3. Cross-reference original vs. converted dates for accuracy

Pro Tips for Best Results

Always backup your original data before running any transformation processes
Test your standardization rules on a small sample first to identify edge cases
Create a separate column for the standardized dates to compare against originals
Document any assumptions made about ambiguous formats (MM/DD vs DD/MM) for future reference
Use data validation tools to verify the standardized dates fall within expected ranges