So I am trying to create a formula that will result in a string of text that is a combination of several other fields put together. I have it all working except for one part... the dates. I need them as text not serial numbers. Ultimately this is creating a custom url that will become a hyperlink for admins to use to quickly execute a search against a web-based database. So the part I really need help with is getting the dates to match the sample.
Sample (Desired Output)
=https://tt.website.com/search?category=&assigned_group=IAD12+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date=12%2F04%2F2016%2C12%2F11%2F2016&search=Search!
Current Formula
=TEXT("https://tt.website.com/search?category=&assigned_group=",)&A3&"+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date="&B3&C3&"&search=Search!"
Cell B3 is a formula resulting in the Sunday prior to today "=TODAY()-WEEKDAY(TODAY(),2)"
Cell C3 is a formula resulting in two Sundays prior to today "=TODAY()-7-WEEKDAY(TODAY(),2)"
The red highlight is the part I am struggling with. the current formula results in these being displayed as serial numbers like this "4270842715". I need them displayed like this "12%2F04%2F2016%2C12%2F11%2F2016". THis is the required syntax for the resultant web url to work properly, basically the string "%2F" is inserted in between each segment of the date in MMDDYYYYMMDDYYYY format so it is "MM%2FDD%2FYYYY%2CMM%2FDD%2FYYYY". the syntax for the separator inserted between the two dates is %2C.
I know this is a very convoluted formula so I truly appreciate any help.
Sample (Desired Output)
=https://tt.website.com/search?category=&assigned_group=IAD12+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date=12%2F04%2F2016%2C12%2F11%2F2016&search=Search!
Current Formula
=TEXT("https://tt.website.com/search?category=&assigned_group=",)&A3&"+Data+Tech&status=Resolved%3BClosed&impact=&assigned_individual=&requester_login=&login_name=&cc_email=&phrase_search_text=&keyword_bq=&exact_bq=&or_bq1=&or_bq2=&or_bq3=&exclude_bq=&create_date=&modified_date=&tags=&case_type=&building_id=&resolved_date="&B3&C3&"&search=Search!"
Cell B3 is a formula resulting in the Sunday prior to today "=TODAY()-WEEKDAY(TODAY(),2)"
Cell C3 is a formula resulting in two Sundays prior to today "=TODAY()-7-WEEKDAY(TODAY(),2)"
The red highlight is the part I am struggling with. the current formula results in these being displayed as serial numbers like this "4270842715". I need them displayed like this "12%2F04%2F2016%2C12%2F11%2F2016". THis is the required syntax for the resultant web url to work properly, basically the string "%2F" is inserted in between each segment of the date in MMDDYYYYMMDDYYYY format so it is "MM%2FDD%2FYYYY%2CMM%2FDD%2FYYYY". the syntax for the separator inserted between the two dates is %2C.
I know this is a very convoluted formula so I truly appreciate any help.
Last edited:
