1) Identify Dates/Text and Check the Basic Number Format

=ISNUMBER(A2)   ' TRUE means a date (number); FALSE means text
=ISTEXT(A2)     ' TRUE means a text date

If it is a number but only appears as a number, simply change the number format to a date. The custom format yyyy-mm-dd is recommended.

2) Safely Convert Text Dates

• Simple: =DATEVALUE(A2); with time included: =DATEVALUE(A2)+TIMEVALUE(A2)
• Force evaluation: =--A2 (when it is in a standard format such as “2025-08-30”)
• Split into components: =DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2))

3) Clean Up Mixed Locales (Separators)

Use NUMBERVALUE for European-style values such as 30.08.2025.

=NUMBERVALUE(A2, ".", "/")   ' Decimal separator ".", group separator "/": set these to match the locale

4) #### Column-Width/Number-Format Issues

If a column is too narrow, a date appears as ####. Widen the column or change the number format to a shorter one such as yy-m-d.

5) The 1904 Date System

If dates are about four years apart after combining two files, one may use the 1904 system. Standardize the settings under File → Options → Advanced.

6) Split/Combine Dates and Times

=INT(A2)       ' Date only
=MOD(A2,1)     ' Time only
=TEXT(MOD(A2,1),"hh:mm")   ' Format for display

7) Calculate Differences, Weekdays, and Workdays

=A2-B2                        ' Difference in days
=DATEDIF(B2,A2,"d")           ' Exact number of days
=WEEKDAY(A2,2)                ' Monday=1 through Sunday=7
=TEXT(A2,"ddd")               ' Weekday text
=NETWORKDAYS(B2,A2,휴일범위)   ' Number of workdays
=EDATE(A2,1)                  ' One month later
=EOMONTH(A2,0)                ' Last day of the month

8) Normalize Large Volumes with Power Query

1. Data → From Table/Range → Transform
2. Change the column type: convert Text → Date/Time; values that cannot be converted are shown as errors
3. Apply rules to error values with Replace Errors, then select Close & Load