合并日期/时间并保留数字格式问题求助

I’ve run into this exact problem before! The root cause is that Google Sheets stores dates and times as serialized numbers (dates are whole integers representing days since 1900, times are decimals representing fractions of a day). When you combine IMPORTRANGE with ARRAYFORMULA, the imported values sometimes lose their "date/time metadata"—so even though the number is correct, Sheets doesn’t recognize it as a date/time, and manual format adjustments won’t stick because the array formula returns raw numeric values.

Here are a few reliable fixes, ordered by efficiency:

1. Use LET to Avoid Redundant IMPORTRANGE Calls (Best Practice) #

This method reduces the number of times you call IMPORTRANGE (better for performance) and ensures the combined value is treated as a date/time:

=ARRAYFORMULA(LET(
    imported_data, IMPORTRANGE("your_spreadsheet_url", "SourceSheet!A:B"),
    date_column, INDEX(imported_data,,1),
    time_column, INDEX(imported_data,,2),
    IF(date_column = "",, date_column + time_column)
))

• Replace "your_spreadsheet_url" with the actual URL of your source sheet.
• Replace "SourceSheet!A:B" with your source range (A = dates, B = times).

After entering this formula:

1. Select the entire column with the formula result.
2. Go to Format > Number > Date time (or pick a custom format like yyyy-mm-dd hh:mm:ss).

2. Direct Conversion with DATEVALUE + TIMEVALUE (For Text-Based Source Data) #

If your source sheet’s date/time columns are stored as text (not native date/time formats), use this to convert them first:

=ARRAYFORMULA(IF(
    ISBLANK(IMPORTRANGE("your_spreadsheet_url", "SourceSheet!A:A")),,
    DATEVALUE(IMPORTRANGE("your_spreadsheet_url", "SourceSheet!A:A")) + 
    TIMEVALUE(IMPORTRANGE("your_spreadsheet_url", "SourceSheet!B:B"))
))

Note: This calls IMPORTRANGE three times, which can be slower for large datasets—stick with the LET method above if you can.

3. Helper Columns (Simpler for Beginners) #

If you prefer a more visual approach:

1. In Column C, import dates: =IMPORTRANGE("your_spreadsheet_url", "SourceSheet!A:A")
2. In Column D, import times: =IMPORTRANGE("your_spreadsheet_url", "SourceSheet!B:B")
3. In Column E, combine them: =ARRAYFORMULA(C:C + D:D)
4. Format Column E as a date/time.

Key Notes #

• Always verify your source sheet’s columns are formatted as Date or Time (not plain text). If they’re text, the DATEVALUE/TIMEVALUE conversion is necessary.
• Array formulas don’t carry formatting with them—you always need to manually set the cell format after entering the formula.

内容的提问来源于stack exchange，提问作者Jake Reilly