Forum Discussion

MathGILLES's avatar
MathGILLES
Copper Contributor
Apr 25, 2022
Solved

The date format is wrong when I concatenate

Hello everyone, 

I'm quite new to this, sorry if maybe I missed the answer somwhere else.
I'm trying to automate the creation of Gcal files for our work planning, which are in that format :
                          05/30/2022
10am-Work A    Math
11am-Work B    Not Math

My idea was : You can import CSV files in Gcal, so I would need something like this in the end

Math
Subject, start date, start time
WorkA,05/30/2022,10:00
WorkC,05/31/2022,14:00

Which I could just save as csv and import in Gcal, to have all my meetings for the month automatically put in.

The problem is this : When I try to concatenate those, it doesn't work, as the date format transforms the date (in B31) into "44683"
In my cell I wrote : 
=CONCAT("WorkA";",";$B31;",";"10:00")
Which transforms into :

WorkA,44683,10:00


Any tips on how to go around this? Or maybe a faster solution to it? 🙂

  • MathGILLES You can use the TEXT function for this:

     

    =CONCAT("WorkA";",";TEXT($B31;"mm/dd/yyyy");",";"10:00")

     

    or

     

    ="WorkA,"&TEXT($B31;"mm/dd/yyyy")&",10:00"

2 Replies

  • MathGILLES You can use the TEXT function for this:

     

    =CONCAT("WorkA";",";TEXT($B31;"mm/dd/yyyy");",";"10:00")

     

    or

     

    ="WorkA,"&TEXT($B31;"mm/dd/yyyy")&",10:00"

Resources