Date and time format. Dear friends, The date...

Etwo_Cargo
Participant V

Date and time format. Dear friends,

The date is appears as 6.01.2019 (day is one digit only. i need it as 06) and time as 08:58:57 i need to concert the as below and add in mail body.

Date 06JAN

Time 0858

How can i do that?

0 17 594
17 REPLIES 17

You need to convert it using CONCATENATE.

Etwo_Cargo
Participant V

@Aleksi_Alkio Dear Aleksi Understood. How can i have 2 digit instead 1 on from date (06 instead 6)? And how can convert month from 01 to JAN?

Etwo_Cargo
Participant V

+Steve Coile Dear Steve,

I checked some of my records but no one of them was 1230.

Where did you enter the expression to reformat the time?

Etwo_Cargo
Participant V

+Steve Coile

I add all those formulas in email notification on workflow as mail body. Is it wrong

Etwo_Cargo
Participant V

+Steve Coile Here is the whole formula.

<<CONCATENATE(RIGHT((โ€œ0โ€ & DAY([DLV DATE])), 2), INDEX(LIST(โ€œJANโ€, โ€œFEBโ€, โ€œMARโ€, โ€œAPRโ€, โ€œMAYโ€, โ€œJUNโ€, โ€œJULโ€, โ€œAUGโ€, โ€œSEPโ€, โ€œOCTโ€, โ€œNOVโ€, โ€œDECโ€), MONTH([DLV DATE])))>><<CONCATENATE(

LEFT([TIME], 2),

MID([TIME], 4,2))>>

Hmmmโ€ฆ Looks fine to me. Try adding a virtual column with an app formula set to =CONCATENATE(LEFT([TIME], 2),

MID([TIME], 4, 2)) and see what values show when you Test the expression in Expression Builder.

Etwo_Cargo
Participant V

+Steve Coile Dear Steve, It is not working it is coming as 1230 always. I Do not know why. Date is working fine. thanks for that. By the why, what should be location of time formula?

Note: it is not suitable to have time column as virtual. Because, there is no initial value option in virtual column. So after shipment arrive and press save button time must stop to work. To stop time working after press save button need to put TIMENOW() in initial value as information of other colleagues. Any comment to help about it?

The virtual column is only to help troubleshoot. You donโ€™t even have to save it. Just click to create it, then click the flask icon in the app formula box to open Expression Builder, and test as I suggested above. When testing is complete, close Expression Builder by clicking the X and then cancel the virtual column.

If the day number is less then 10, add โ€œ0โ€. For the month name you can use SWITCH expression.

See Month Number to Name in the attached. INDEX() help.appsheet.com

Etwo_Cargo
Participant V

+Steve Coile Dear Steve,

Day of the date is ok i think. Would be appreciated if you help me for whole formula to have what i want. I am do not experience more that your as you can image.

Date format is

6.01.2019 i need it as 06JAN time is 08:58:57 i need it as 0858

thanks in advance.

Etwo_Cargo
Participant V

+Steve Coile Hi, Can i use formula like that, to have 06010858? System shows me 060130 0601 is ok but i do not know from where 30 is coming!!

<<RIGHT((โ€œ0โ€&DAY([DLV DATE])),2)>><<CONCATENATE(LEFT([TIME],2),MID([TIME],4,2)>>

To convert a Time value in the TIME column from HH:MM:SS to HHMM:

CONCATENATE(

LEFT([TIME], 2),

MID([TIME], 4, 2) )

To convert a Date value in the DLV DATE column from DD/MM/YYYY to DDMMM:

CONCATENATE(

RIGHT((โ€œ0โ€ & DAY([DLV DATE])), 2),

INDEX(LIST(โ€œJANโ€, โ€œFEBโ€, โ€œMARโ€, โ€ฆ, โ€œDECโ€), MONTH([DLV DATE])) )

Etwo_Cargo
Participant V

+Steve Coile Many many thanks

Etwo_Cargo
Participant V

+Steve Coile Dear Steve, A small question. What could be reason about time format? I am getting 1230 always after add formula.

I would suspect [TIME] is 12:30.

Top Labels in this Space