SOLVED

The date format is wrong when I concatenate

New Contributor

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?

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@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"

@Hans Vogelaar 

It worked perfectly, thanks so much