Every part of the example below can be done more succinctly, efficiently, elegantly, and adaptably -- it's really not good code -- but I wanted to make the components as clear as possible so you can see how it works.
First, here's an oversimplified function to convert hours to 24 hour format. I don't recommend that you use exactly this because it'll only work with on-the-hour times formatted exactly as you wrote them, e.g. "3PM" or "2AM" -- "2:30 pm" would not work at all. For more sophisticated time conversion, check out this answer: convert 12-hour hh:mm AM/PM to 24-hour hh:mm
function oversimplified_time_format_converter(input){
if(input.indexOf("PM")!==-1){
var hour = 12 + (input.substring(0,input.indexOf("PM"))*1)
if(hour>24){hour-=24}
} else {
var hour = input.substring(0,input.indexOf("AM"))
}
return hour
}
Next, here's a function to perform the task you mentioned, which uses the oversimplified time format converter function above.
function yourFunction() {
//First, locate your data:
var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheet/yourspreadsheetURL").getSheetByName("YourSheetName")
var cell = sheet.getRange("A1")
var cell_contents = cell.getValue() //The cell contents will be "1PM -5PM"
//Next, locate the cells where the results will go
var first_cell_to_the_right = cell.offset(0,1) //This is cell B1
var second_cell_to_the_right = cell.offset(0,2) //This is cell C1
//Next, get the text, split it into separate strings
var first_part = cell_contents.substring(0,cell_contents.indexOf(" - ")) //This will be "1PM"
var second_part = cell_contents.substring(cell_contents.indexOf(" - ")+3) //This will be "5PM"
//Now convert to 24-hour time format:
first_part = oversimplified_time_format_converter(first_part)
second_part = oversimplified_time_format_converter(second_part)
//Now write the results to your spreadsheet
first_cell_to_the_right.setValue(first_part) //Set value of B1 to "1PM"
second_cell_to_the_right.setValue(second_part) //Set value of C1 to "5PM"
}
