df %>%
  set_names(enframe(unlist(df[1,])) %>%
  mutate(name = na_if(name, ''))%>%
  fill(name)%>%
  transmute(nms = coalesce(str_c(name, value, sep='_'), value)) %>%
  pull(nms)) %>%
  slice(-1)%>%
  type.convert(as.is = TRUE)%>%
  pivot_longer(-c(category, number), names_to = c('Date', '.value'), 
               names_sep = '_', names_transform = list(Date = dmy)) %>%
  arrange(category, Date, number)

# A tibble: 54 x 5
   category number Date        blue green
   <chr>     <int> <date>     <int> <int>
 1 G             1 2012-07-02     1     0
 2 G             2 2012-07-02     2    99
 3 G             3 2012-07-02     1     1
 4 G             4 2012-07-02     1     1
 5 G             5 2012-07-02     1     0
 6 G             6 2012-07-02     1    99
 7 G             7 2012-07-02     1     0
 8 G             8 2012-07-02     1     1
 9 G             9 2012-07-02     1     1
10 G             1 2012-07-03     1     0
# ... with 44 more rows

Here is another option using a combination of base R and tidyverse. Here, I first clean up the column names by including the date in the column names for the column to the left (i.e., "green), so that each column has a date. Then, I concatenate the header with the sub-heading, except for the first 2 columns (i.e., category and number). Then, I remove the first row and pivot to a long format with the date in one column and the colors stay in their own column.

library(tidyverse)

colnames(df)[seq(2, ncol(df), 2)] <- colnames(df)[seq(1, ncol(df), 2)]

colnames(df) <-
  c(df[1, 1], df[1, 2], paste(sep = '_', colnames(df)[3:ncol(df)], as.character(unlist(df[1, 3:ncol(df)]))))

df %>%
  slice(-1) %>%
  pivot_longer(-c(category, number),
               names_to = c("Date", ".value"),
               names_sep = "_") %>%
  arrange(Date, category, number) %>%
  mutate(Date = dmy(Date))

Output

# A tibble: 54 × 5
   category number Date       blue  green
   <chr>    <chr>  <date>     <chr> <chr>
 1 G        1      2012-07-02 1     0    
 2 G        2      2012-07-02 2     99   
 3 G        3      2012-07-02 1     1    
 4 G        4      2012-07-02 1     1    
 5 G        5      2012-07-02 1     0    
 6 G        6      2012-07-02 1     99   
 7 G        7      2012-07-02 1     0    
 8 G        8      2012-07-02 1     1    
 9 G        9      2012-07-02 1     1    
10 H        1      2012-07-02 1     1    
# … with 44 more rows

Data

df <- structure(
  list(
    c("category", "G", "G", "G", "G", "G", "G", "G", "G", "G", "H", "H", "H", "H", "H", "H", "H", "H", "H"),
    c("number", "1", "2", "3", "4", "5", "6", "7", "8", "9", "1", "2", "3", "4", "5", "6", "7", "8", "9"),
    `02.Jul.12` = c("blue", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2"),
    c("green", "0", "99", "1", "1", "0", "99", "0", "1", "1", "1", "99", "1", "99", "1", "0", "1", "0", "0"),
    `03.Jul.12` = c("blue", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2", "2"),
    c("green", "0", "99", "99", "1", "0", "1", "0", "1", "1", "1", "0", "1", "2", "1", "0", "1", "0", "0"),
    `04.Jul.12` = c("blue", "1", "1", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"),
    c("green", "0", "99", "99", "99", "99", "99", "0", "99", "1", "1", "0", "99", "99", "1", "99", "99", "1", "1")
  ),
  class = "data.frame",
  row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19")
)

If you have additional columns (e.g., colors), then you can adjust how colnames is replaced. So, I first create a sequence (a) that starts with the first date column (i.e., 3) to the last column. Then, I create 2 sequences from a, where b has the the index for the empty column names (excluding the first 2 columns) and c has the date column names. Then, I replicate the dates twice so that they can replace the 2 empty column names (green and red) for each date. Then, in the next step, I just copy the first two column names (i.e., category and number), then paste the remaining headings (i.e., dates) to the sub-headings. Then, the process is the same as above.

a <- seq(3, ncol(df2))
b <- a[!(a%%3==0)]
c <- a[(a%%3==0)]

colnames(df2)[b] <- colnames(df2)[sort(rep(c, 2))]

colnames(df2) <-
  c(df2[1, 1], df2[1, 2], paste(sep = '_', colnames(df2)[3:ncol(df2)], as.character(unlist(df2[1, 3:ncol(df2)]))))

df2 %>%
  slice(-1) %>%
  pivot_longer(-c(category, number),
               names_to = c("Date", ".value"),
               names_sep = "_") %>%
  arrange(Date, category, number) %>%
  mutate(Date = lubridate::dmy(Date))

Output

# A tibble: 54 × 6
   category number Date       blue  green red  
   <chr>    <chr>  <date>     <chr> <chr> <chr>
 1 G        1      2012-07-02 1     0     1    
 2 G        2      2012-07-02 2     99    2    
 3 G        3      2012-07-02 1     1     1    
 4 G        4      2012-07-02 1     1     1    
 5 G        5      2012-07-02 1     0     1    
 6 G        6      2012-07-02 1     99    1    
 7 G        7      2012-07-02 1     0     1    
 8 G        8      2012-07-02 1     1     1    
 9 G        9      2012-07-02 1     1     1    
10 H        1      2012-07-02 1     1     1    
# … with 44 more rows

Data

df2 <- structure(
    list(
      c("category", "G", "G", "G", "G", "G", "G", "G", "G", "G", "H", "H", "H", "H", "H", "H", "H", "H", "H"),
      c("number", "1", "2", "3", "4", "5", "6", "7", "8", "9", "1", "2", "3", "4", "5", "6", "7", "8", "9"),
      `02.Jul.12` = c("blue", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2"),
      c("green", "0", "99", "1", "1", "0", "99", "0", "1", "1", "1", "99", "1", "99", "1", "0", "1", "0", "0"),
      c("red", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2"),
      `03.Jul.12` = c("blue", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2", "2"),
      c("green", "0", "99", "99", "1", "0", "1", "0", "1", "1", "1", "0", "1", "2", "1", "0", "1", "0", "0"),
      c("red", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2"),
      `04.Jul.12` = c("blue", "1", "1", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"),
      c("green", "0", "99", "99", "99", "99", "99", "0", "99", "1", "1", "0", "99", "99", "1", "99", "99", "1", "1"),
      c("red", "1", "2", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "1", "2", "2")
    ),
    class = "data.frame",
    row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19")
  )

The following code should produce your desired output, though others may have more elegant solutions:

#Create a dataframe with the above table
df <- read.table(text=table, header = TRUE)
df

names(df) <- df[1,]
library(lubridate); library(tidyr)

startdate <- dmy("02-Jul-12")
for (i in seq(3, ncol(df), by = 2)){
  names(df)[i:(i+1)] <- paste0(startdate, ":", names(df)[i:(i+1)])
  startdate <- startdate+1
}

df.tdy <- df[-1,] %>% pivot_longer(-c("category","number"), names_to = "datecol", values_to = "value") %>% 
  separate(datecol, c("date","color"), sep = ":") %>%
  pivot_wider(names_from = "color") %>%
    arrange(date,category,number)

# category number date       blue  green
# <chr>    <chr>  <chr>      <chr> <chr>
#   1 G        1      2012-07-02 1     0    
# 2 G        2      2012-07-02 2     99   
# 3 G        3      2012-07-02 1     1    
# 4 G        4      2012-07-02 1     1    
# 5 G        5      2012-07-02 1     0    
# 6 G        6      2012-07-02 1     99   
# 7 G        7      2012-07-02 1     0    
# 8 G        8      2012-07-02 1     1    
# 9 G        9      2012-07-02 1     1    
# 10 H        1      2012-07-02 1     1   

Here's a tidyverse solution that can handle duplicate column names (like blue) yet doesn't rely on splicing those names:

Solution

First import the tidyverse and locate the Excel file:

# Load the tidyverse.
library(tidyverse)


# Filepath to the Excel file.
filepath <- "reprex.xlsx"

Then read the Excel file in three relevant pieces: the date row (topmost), the header (with duplicate names), and the dataset.

# Extract the date row and fill in the blanks.
dates <- readxl::read_excel(path = filepath, col_names = FALSE, skip = 0, n_max = 1) %>%
  # Convert everything to dates where possible; leave blanks (NAs) elsewhere.
  mutate(across(.cols = everything(), .fns = lubridate::as_datetime)) %>%
  # Treat date row as a column.
  as.double() %>% lubridate::as_datetime() %>% as_tibble() %>%
  # Fill in the blanks with the preceding dates.
  fill(1, .direction = "down") %>%
  # Treat the result as a vector of dates.
  .[[1]]


# Extract the header...
names <- readxl::read_excel(path = filepath, col_names = FALSE, skip = 1, n_max = 1) %>%
  # ...as a vector of column names (with duplicates).
  as.character()


# Extract the (unnamed) dataset.
df <- readxl::read_excel(path = filepath, col_names = FALSE, skip = 2, n_max = Inf)

Finally, use this workflow to properly name and pivot the data.

# Cut out the headers from the data.
df <- df %>%
  # Properly name the dataset.
  set_names(nm = names) %>%
  
  # Pivot the color columns.
  pivot_longer(cols = !c(category, number), names_to = "color") %>%

  # Convert to the proper datatypes.
  mutate(
    category = as.character(category),
    number = as.integer(number),
    value = as.numeric(value)
  ) %>%
  
  # Identify each "clump" of colors by the one row from which it originated;
  # where {'category', 'number'} uniquely identify each such row.
  group_by(category, number) %>%
  # Map the date names to each clump.
  mutate(
    # Index the entries in each clump.
    date = row_number(),
    # Map each date to its corresponding entry.
    date = dates[!is.na(dates)][date],
    # Ensure homogeneity as date objects.
    date = lubridate::as_datetime(date)
  ) %>% ungroup() %>%
  
  # Pivot the colors into consolidated columns: one for each color.
  pivot_wider(names_from = color, values_from = value) %>%
  
  # Sort as desired.
  arrange(date, category, number)

Results

Given a reprex.xlsx like the one you describe here

when I import my excel .xlsx file instead of a .csv file, the dates become numbers (e.g. 41092)

this solution should yield the following result for df:

# A tibble: 54 x 5
   category number date                 blue green
   <chr>     <int> <dttm>              <dbl> <dbl>
 1 G             1 2012-07-02 00:00:00     1     0
 2 G             2 2012-07-02 00:00:00     2    99
 3 G             3 2012-07-02 00:00:00     1     1
 4 G             4 2012-07-02 00:00:00     1     1
 5 G             5 2012-07-02 00:00:00     1     0
 6 G             6 2012-07-02 00:00:00     1    99
 7 G             7 2012-07-02 00:00:00     1     0
 8 G             8 2012-07-02 00:00:00     1     1
 9 G             9 2012-07-02 00:00:00     1     1
10 H             1 2012-07-02 00:00:00     1     1
11 H             2 2012-07-02 00:00:00     1    99
12 H             3 2012-07-02 00:00:00     1     1
13 H             4 2012-07-02 00:00:00     1    99
14 H             5 2012-07-02 00:00:00     1     1
15 H             6 2012-07-02 00:00:00     1     0
16 H             7 2012-07-02 00:00:00     1     1
17 H             8 2012-07-02 00:00:00     2     0
18 H             9 2012-07-02 00:00:00     2     0
19 G             1 2012-07-03 00:00:00     1     0
20 G             2 2012-07-03 00:00:00     2    99
21 G             3 2012-07-03 00:00:00     1    99
22 G             4 2012-07-03 00:00:00     1     1
23 G             5 2012-07-03 00:00:00     1     0
24 G             6 2012-07-03 00:00:00     1     1
25 G             7 2012-07-03 00:00:00     1     0
26 G             8 2012-07-03 00:00:00     1     1
27 G             9 2012-07-03 00:00:00     1     1
28 H             1 2012-07-03 00:00:00     1     1
29 H             2 2012-07-03 00:00:00     1     0
30 H             3 2012-07-03 00:00:00     1     1
31 H             4 2012-07-03 00:00:00     1     2
32 H             5 2012-07-03 00:00:00     1     1
33 H             6 2012-07-03 00:00:00     1     0
34 H             7 2012-07-03 00:00:00     2     1
35 H             8 2012-07-03 00:00:00     2     0
36 H             9 2012-07-03 00:00:00     2     0
37 G             1 2012-07-04 00:00:00     1     0
38 G             2 2012-07-04 00:00:00     1    99
39 G             3 2012-07-04 00:00:00     1    99
40 G             4 2012-07-04 00:00:00     2    99
41 G             5 2012-07-04 00:00:00     1    99
42 G             6 2012-07-04 00:00:00     1    99
43 G             7 2012-07-04 00:00:00     1     0
44 G             8 2012-07-04 00:00:00     1    99
45 G             9 2012-07-04 00:00:00     1     1
46 H             1 2012-07-04 00:00:00     1     1
47 H             2 2012-07-04 00:00:00     1     0
48 H             3 2012-07-04 00:00:00     1    99
49 H             4 2012-07-04 00:00:00     1    99
50 H             5 2012-07-04 00:00:00     1     1
51 H             6 2012-07-04 00:00:00     1    99
52 H             7 2012-07-04 00:00:00     1    99
53 H             8 2012-07-04 00:00:00     1     1
54 H             9 2012-07-04 00:00:00     1     1

Note

Much like openxlsx::convertToDate(), the readxl functions here automatically convert Excel date numbers into the proper R Dates.

A base R option using resahpe

u <- type.convert(setNames(df[-1, ], df[1, ]), as.is = TRUE)
transform(
  reshape(
    cbind(
      u[1:2],
      setNames(
        u[-c(1:2)],
        paste0(
          names(u)[-c(1:2)],
          ".",
          ave(seq(length(u) - 2), names(u)[-c(1:2)], FUN = seq_along)
        )
      )
    ),
    direction = "long",
    idvar = c("category", "number"),
    varying = -c(1:2),
    timevar = "date"
  ),
  date = Filter(nchar, names(df))[date]
)

gives

      category number      date blue green
G.1.1        G      1 02.Jul.12    1     0
G.2.1        G      2 02.Jul.12    2    99
G.3.1        G      3 02.Jul.12    1     1
G.4.1        G      4 02.Jul.12    1     1
G.5.1        G      5 02.Jul.12    1     0
G.6.1        G      6 02.Jul.12    1    99
G.7.1        G      7 02.Jul.12    1     0
G.8.1        G      8 02.Jul.12    1     1
G.9.1        G      9 02.Jul.12    1     1
H.1.1        H      1 02.Jul.12    1     1
H.2.1        H      2 02.Jul.12    1    99
H.3.1        H      3 02.Jul.12    1     1
H.4.1        H      4 02.Jul.12    1    99
H.5.1        H      5 02.Jul.12    1     1
H.6.1        H      6 02.Jul.12    1     0
H.7.1        H      7 02.Jul.12    1     1
H.8.1        H      8 02.Jul.12    2     0
H.9.1        H      9 02.Jul.12    2     0
G.1.2        G      1 03.Jul.12    1     0
G.2.2        G      2 03.Jul.12    2    99
G.3.2        G      3 03.Jul.12    1    99
G.4.2        G      4 03.Jul.12    1     1
G.5.2        G      5 03.Jul.12    1     0
G.6.2        G      6 03.Jul.12    1     1
G.7.2        G      7 03.Jul.12    1     0
G.8.2        G      8 03.Jul.12    1     1
G.9.2        G      9 03.Jul.12    1     1
H.1.2        H      1 03.Jul.12    1     1
H.2.2        H      2 03.Jul.12    1     0
H.3.2        H      3 03.Jul.12    1     1
H.4.2        H      4 03.Jul.12    1     2
H.5.2        H      5 03.Jul.12    1     1
H.6.2        H      6 03.Jul.12    1     0
H.7.2        H      7 03.Jul.12    2     1
H.8.2        H      8 03.Jul.12    2     0
H.9.2        H      9 03.Jul.12    2     0
G.1.3        G      1 04.Jul.12    1     0
G.2.3        G      2 04.Jul.12    1    99
G.3.3        G      3 04.Jul.12    1    99
G.4.3        G      4 04.Jul.12    2    99
G.5.3        G      5 04.Jul.12    1    99
G.6.3        G      6 04.Jul.12    1    99
G.7.3        G      7 04.Jul.12    1     0
G.8.3        G      8 04.Jul.12    1    99
G.9.3        G      9 04.Jul.12    1     1
H.1.3        H      1 04.Jul.12    1     1
H.2.3        H      2 04.Jul.12    1     0
H.3.3        H      3 04.Jul.12    1    99
H.4.3        H      4 04.Jul.12    1    99
H.5.3        H      5 04.Jul.12    1     1
H.6.3        H      6 04.Jul.12    1    99
H.7.3        H      7 04.Jul.12    1    99
H.8.3        H      8 04.Jul.12    1     1
H.9.3        H      9 04.Jul.12    1     1

Here is another solution using dplyr and tidyr. We would first combine the headers and subheaders and then pivot the data frame. We will do two pivoting operations: first collect everything into date, name (consisting of either "blue" or "green") and value (consisting of the corresponding values for "blue" and "green"); then, pivot_wider the name and value columns. df comes directly from your excel reprex.

library(dplyr)
library(tidyr)

nms1 <- tidyr:::fillDown(na_if(names(df), ""))
nms2 <- unlist(df[1L, ])
df[-1L, ] %>% 
  setNames(if_else(is.na(nms1), nms2, paste(nms1, nms2, sep = "_"))) %>% 
  pivot_longer(-c(category, number), c("date", "name"), names_sep = "_") %>% 
  pivot_wider()

Output

# A tibble: 54 x 5
   category number date      blue  green
   <chr>    <chr>  <chr>     <chr> <chr>
 1 G        1      02.Jul.12 1     0    
 2 G        1      03.Jul.12 1     0    
 3 G        1      04.Jul.12 1     0    
 4 G        2      02.Jul.12 2     99   
 5 G        2      03.Jul.12 2     99   
 6 G        2      04.Jul.12 1     99   
 7 G        3      02.Jul.12 1     1    
 8 G        3      03.Jul.12 1     99   
 9 G        3      04.Jul.12 1     99   
10 G        4      02.Jul.12 1     1    
# ... with 44 more rows