CODEWITHSUNDEEP
rexceldateformat
Florian L.
Florian L.

Reputation: 21

Reformatting/merging of Excel date elements

I want to change a date format (examples below) into a FAIR data format, i.e., YYYY-MM-DDTHH-MM-SS. This can be done either using an Excel function (data in .csv) or in R. The date is in one column and the time is in the second one. Any suggestion is welcomed!

date   time
80817  2300    meaning 11 PM on 8 August 2017
80817  2400    meaning 12 AM on 8 August 2017
90817  1300    meaning 1 PM on 9 August 2017
160817 920     meaning 9:20 AM on 16 August 2017

I could do this manually but with 3500 dataset rows, this might take a lifetime. Feel free to drop any recommendations!

Upvotes: 2

Views: 79

Answers (4)

Tim G
Tim G

Reputation: 4147

In Base R you can paste date and time with sprintf to pad it, strptime of it with dmy_HM format and then use format again to bring the string to FAIR-form.

dt <- data.frame(date = c(80817L, 80817L, 90817L, 160817L),time = c(2300L, 2400L, 1300L, 920L))
dt$FAIR <- format(strptime(paste0(sprintf("%06d", dt$date), sprintf("%04d", dt$time)), format="%d%m%y%H%M"), "%Y-%m-%dT%H-%M-%S")

giving

    date time                FAIR
1  80817 2300 2008-08-17T23-00-00
2  80817 2400 2008-08-18T00-00-00
3  90817 1300 2009-08-17T13-00-00
4 160817  920 2016-08-17T09-20-00

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

To output as a text string using Excel:
Note I used a Table for the data and named the table dateTime

=LET(
    d, TEXT(dateTime[date], "000000"),
    t, TEXT(dateTime[time], "00\:00"),
    yr, RIGHT(d, 2) + 2000,
    mn, MID(d, 3, 2),
    dy, LEFT(d, 2),
    dt, DATE(yr, mn, dy) + t,
    TEXT(dt, "yyyy-mm-dd\Thh-mm-ss")
)

Or, you can output as a real data and just format the cell appropriately:

=LET(
    d, TEXT(dateTime[date], "000000"),
    t, TEXT(dateTime[time], "00\:00"),
    yr, RIGHT(d, 2) + 2000,
    mn, MID(d, 3, 2),
    dy, LEFT(d, 2),
    dt, DATE(yr, mn, dy) + t,
    dt
)

with custom format of yyyy-mm-ddThh-mm-ss

enter image description here

Upvotes: 0

the-mad-statter
the-mad-statter

Reputation: 8886

Taking the Excel only path and assuming your date values are stored in column A and time values are stored in column B, here are some Excel formulas to build this up.

Column Label    Formula
A      date     80817
B      time     2300
C      year     ="20"&RIGHT(A2, 2)
D      month    =IF(LEN(A2)=6,MID(A2,3,2),MID(A2,2,2))
E      day      =IF(LEN(A2)=6,LEFT(A2,2),"0"&LEFT(A2,1))
F      hours    =IF(LEN(B2)=4,LEFT(B2,2),"0"&LEFT(B2,1))
G      minutes  =RIGHT(B2,2)
H      datetime =C2&"-"&D2&"-"&E2&"T"&F2&"-"&G2&"-00"

See the Excel file on Github for specific details.

Reprex files hosted with on GitHub

Upvotes: 1

Jon Spring
Jon Spring

Reputation: 66880

I'd first pad the two columns to have a leading zero (to improve parsing), then combine them, turn into a datetime, and finally convert that to a text field with the desired formatting:

library(tidyverse)
data.frame(date = c(80817L, 80817L, 90817L, 160817L),
           time = c(2300L, 2400L, 1300L, 920L)) |>
  mutate(FAIR = paste(
    date |> str_pad(6, "left", pad = 0),
    time |> str_pad(4, "left", pad = 0)) |>
      ymd_hm() |>
      format("%Y-%m-%dT%H-%M-%S"))


    date time                FAIR
1  80817 2300 2008-08-17T23-00-00
2  80817 2400 2008-08-18T00-00-00
3  90817 1300 2009-08-17T13-00-00
4 160817  920 2016-08-17T09-20-00

Upvotes: 3

Related Questions