You need to sign in to do that
Don't have an account?
Heather Mickey
how to to replace string with converted date and time
There is a field that currently stores Office Times in the following format, based on the input from a Custom S-Control.
Field Name: Office Times
Data Stored (example): &m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020
I'd like to create a new field which takes this data stored and makes it more user reading friendly.
M: 01:00-22:00
Tu: 01:45-22:50
W: 01:13-22:23
Th: 01:15-22:45
F: 02:30-21:00
Sa: 08:00-17:00
I'm having a difficult time figuring out how to do this as a formula. Any ideas? If not possible with a formula, what should I be doing?
*There is one other tricky part to this. Someone could enter "&tu=360,615;780,1020", meaning
Tu: 06:00-10:15, 13:00-17:00
Thank you so much,
Heather
Field Name: Office Times
Data Stored (example): &m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020
I'd like to create a new field which takes this data stored and makes it more user reading friendly.
M: 01:00-22:00
Tu: 01:45-22:50
W: 01:13-22:23
Th: 01:15-22:45
F: 02:30-21:00
Sa: 08:00-17:00
I'm having a difficult time figuring out how to do this as a formula. Any ideas? If not possible with a formula, what should I be doing?
*There is one other tricky part to this. Someone could enter "&tu=360,615;780,1020", meaning
Tu: 06:00-10:15, 13:00-17:00
Thank you so much,
Heather
RIGHT(
LEFT("&m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020", FIND( "&tu=", "&m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020") - 1
) ,
LEN(LEFT("&m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020", FIND( "&tu=", "&m=60,1320&tu=105,1370&w=73,1343&th=75,1365&f=150,1260&sa=480,1020") - 1
)) - 3
)
The above formula will produce the following text value:
60,1320
You can then further use a combination of find function to identify the position of the comma, left and right functions with the above value to determine the value of "60" and the value of "1320". The formula will get quite long if you are figuring out the time of each day so you can probably break out into a seperate field per day then combine the result in a master formula field.