Date (at the day level e.g 1/1/2019, 1/2/2019)

[vpalani08]

Convert to start of month as Date data type. Because I have other sheet with date as 1/1/2019 includes all the sum of values of the month.
So, If i can convert my other sheet with same start of month as data type I can join them both.
Can anyone help me with it?

[apathetichell · Accepted solution]

are they currently in datetime format? if so:

datetimeadd([Field1],-tonumber(datetimeformat([Field1],"%e"))+1,"days")

will get you the first of the month in formula.

[vpalani08]

No just the Date Format. 
I have values in all date from 01/01/2019 to month end 01/31/2019. I need to get the cumulative sum of all the values across only first day of month 01/01/2019.

[apathetichell]

Yeah - my formula will generate firsts of the month for dates.

[vpalani08]

Sorry the Unit Date Column is in V_String type

[vpalani08]

It worked I have to change the data type.

[apathetichell]

you can convert with a datetime tool or use this formula:

datetimeparse([test],"%m/%d/%Y")

if you want to keep it as a string (for some reason) and just do a manual convert to the first of the month you can use a regex replace like this:

REGEX_Replace([test], "(\d+)/\d+/(\d+)","$1/01/$2")