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

(Etwo Cargo) #1

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?

(Aleksi Alkio) #2

You need to convert it using CONCATENATE.

(Etwo Cargo) #3

@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) #4

+Steve Coile Dear Steve,

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

(Steven Coile) #5

Where did you enter the expression to reformat the time?

(Etwo Cargo) #6

+Steve Coile

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

(Etwo Cargo) #7

+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))>>

(Steven Coile) #8

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) #9

+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?

(Steven Coile) #10

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.

(Aleksi Alkio) #11

If the day number is less then 10, add “0”. For the month name you can use SWITCH expression.

(Steven Coile) #12

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

(Etwo Cargo) #13

+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) #14

+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)>>

(Steven Coile) #15

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) #16

+Steve Coile Many many thanks

(Etwo Cargo) #17

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

(Steven Coile) #18

I would suspect [TIME] is 12:30.