Cambio de uso horario

1

Project Name (if applicable, otherwise just type n/a)

Unidad Administrativa Financiera

Question

Al momento de descargar los formularios en excel las filas “created_at” y “uploaded_at” salen con la hora que no es de Ecuador, como logro cambiar el uso horario para que salga con la hora correcta.

I confirm I have read the User Guide at docs.epicollect.net

on

I confirm I have searched for an answer in this community.

on

I confirm I have tried asking the same question to Perplexity AI with the tag #epicollect5, but it did not help.

on

2

⚙️ How to Convert Epicollect5 UTC to Ecuador Local Time (ECT) in Excel

Ecuador Time (ECT) is UTC-5, meaning it is 5 hours behind Coordinated Universal Time (UTC). To correct the time locally, you need to subtract 5 hours from the downloaded time.

Step 1: Prepare the Data Columns

  1. Locate the Columns: Identify the columns containing the UTC timestamps (e.g., created_at and uploaded_at). Let’s assume the data for one of these starts in cell B2.

  2. Insert a New Column: Right-click on the column immediately to the right of your UTC timestamp column (e.g., Column C) and select Insert. Name this new column something clear, like “Created At (ECT).”

Step 2: Apply the Conversion Formula

In the first cell of your new column (e.g., C2), enter the following formula:

=B2−TIME(5,0,0)

  • B2: This is the cell containing the original UTC timestamp. Adjust this as necessary.

  • TIME(5, 0, 0): This Excel function creates a time value of 5 hours, 0 minutes, and 0 seconds, which is then subtracted from the UTC time.

Step 3: Copy the Formula

  1. Select the Cell: Click on the cell where you entered the formula (C2).

  2. Fill Down: Double-click the small square (the fill handle) in the bottom-right corner of the cell. This will automatically copy the formula down to the end of your data set, applying the UTC-5 conversion to all rows.

Step 4: Format the New Column

The results may initially show as a serial number (a long number representing the date/time in Excel). You need to format it correctly:

  1. Select the New Column: Click on the column header (e.g., Column C) to select the entire converted time column.

  2. Open Format Cells: Right-click on the selection and choose Format Cells… (or press Ctrl+1).

  3. Choose Custom Format:

    • Go to the Number tab, and select Custom.

    • In the “Type” box, enter a format that displays both the date and time clearly, such as:

      yyyy-mm-dd hh:mm:ss

  4. Click OK.

The new column will now display the correct local time for Ecuador (ECT). You can hide or delete the original UTC columns if they are no longer needed.

3

💻 Converting UTC to ECT in Google Sheets

Since Ecuador Time (ECT) is UTC-5, we will use a simple subtraction formula to move the time back 5 hours.

Step 1: Locate and Insert a New Column

  1. Identify the UTC Column: Locate the column with the UTC timestamps (e.g., created_at or uploaded_at). Let’s assume the data starts in cell B2.

  2. Insert Conversion Column: Right-click on the column next to your UTC data (e.g., Column C) and select Insert 1 right.

  3. Name the Column: Title the new column something like “Created At (ECT).”

Step 2: Apply the Conversion Formula

In the first cell of your new column (e.g., C2), enter the following formula:

=B2−TIME(5,0,0)

  • B2: This is the cell containing the original UTC timestamp. Change this cell reference if your data starts elsewhere.

  • TIME(5, 0, 0): This function works in Google Sheets just as in Excel, creating a time value of 5 hours to subtract from the date/time value.

Step 3: Fill the Formula Down

  1. Select the Cell: Click on the cell where you entered the formula (C2).

  2. Fill Down: Click and drag the small blue square (the fill handle) in the bottom-right corner of the cell down to the last row of your data.

Step 4: Format the New Column

Google Sheets usually auto-formats date and time, but it’s best to verify the correct display:

  1. Select the Column: Click on the header of your newly created column (e.g., Column C).

  2. Go to Format: In the top menu, click Format → Number → Date time.

    • Tip: If you want a specific custom format (e.g., to omit seconds or include a day of the week), go to Format → Number → Custom date and time. You can then enter a format like yyyy/mm/dd hh:mm:ss.

The new column will now display the correct local time for Ecuador.