Skip to main content

Edit your sales date timestamp in Excel for uploading to Deputy

Lisa Kirby
  • Updated

Background

To bulk import sales data into Deputy, managers can download a sample spreadsheet template to populate with sales data information. To populate the spreadsheet with sales data they may choose to:

  1. Export sales data from another system in their organisation
  2. View this data in a spreadsheet and then copy the relevant date times and sales data into the Deputy's sample template format
  3. Import this updated and correctly formatted sales data spreadsheet into Deputy

However, you will note that Deputy requires the sales date timestamp data to be formatted as:

YYYY-MM-DDTHH:MM:SS in one single column

Other systems may not export in this format or even store the date and time in the same column.

Therefore, we will need to use Excel formulas to manipulate the data into the correct format. This article will step through some common examples.

 

Example formulas to correct date timestamp format in Excel

My date and time are exported in different columns

Example 1

In this example, the sales system has exported the date and time (hourly) for the sales into two different columns so we use the Excel formula: 

=TEXT(CELL1,"YYYY-MM-DD")&"T"&TEXT(TIME(CELL2,0,0),"HH:MM:SS")

where CELL1 is the corresponding cell with the date

and CELL2 is the corresponding cell with the hour

Tday and hour import.png

Example 2

In this example, the sales system has exported the date and exact time for the sales into two different columns so we use the Excel formula: 

=TEXT(CELL1,"YYYY-MM-DD")&"T"&TEXT(CELL2,"HH:MM:SS")

where CELL1 is the corresponding cell with the date

and CELL2 is the corresponding cell with the time of the sale

T sales import.png

Once you have your date timestamp formatted correctly, ensure you have the other mandatory data included that you need for bulk sales upload.

T time of day sales upload.png

Tip: you can leave the original date and time columns, plus any other information that may be exported from your sales system but not needed, in your spreadsheet and then just disregard that data during the mapping part of the import process.

In this example, the original date and time columns from the spreadsheet are not mapped to any destination field empty and the Formatted date and timestamp column of data is selected for import.

T map with fiels for sales upload.png

Related articles