CODEWITHSUNDEEP
datetimegoogle-apps-scriptgoogle-sheets
Jessica Verango
Jessica Verango

Reputation: 1

Remove date from Time in Google App Script

I am new to script and found a code that I have been tweaking for my needs but I keep running into a problem since I separated the date and time. My date field display and selected value match but the time fields keep defaulting to MM/DD/YY HH:MM A/P when selected. en I click on it (it displays correctly when not selected). I need to have the field only display the time and nothing else. so it will stop causing issues to my formulas on other sheets. I know my problem lies in the last part but so far what I have tried has failed.

  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MAIN SHEET          
  var mainSheet = ss.getSheetByName("MAIN");

  //LAST ROW ON MAIN SHEET
  var lastRow = mainSheet.getLastRow();

  for (var j = 5; j <= lastRow; j++)
  {
    // CHECK CLOCK IN
    if(mainSheet.getRange('b1:b1').getValue() ==  mainSheet.getRange(j, 1).getValue() && mainSheet.getRange(j,3).getValue() == '')
    {
      Browser.msgBox('Need to Clock Out before Clocking IN');
      return;
    }
  }
  
// ADD CLOCK IN RECORD

    mainSheet.getRange(lastRow + 1, 1).setValue(mainSheet.getRange('b1:b1').getValue()).setFontSize(12);
    mainSheet.getRange(lastRow + 1, 2).setValue(new Date(new Date().setHours(0, 0, 0, 0))).setNumberFormat('MM/DD/YY').setHorizontalAlignment("center").setFontSize(12);
    mainSheet.getRange(lastRow + 1, 3).setValue(new Date()).setNumberFormat("hh:mm A/P").setHorizontalAlignment("center").setFontSize(12);//````

Upvotes: 0

Views: 481

Answers (1)

Vetx
Vetx

Reputation: 26

You get a whole bunch of formatting options if you look for Utilities.formatDate

var date = Utilities.formatDate(new Date(), "Europe/Berlin", "dd.MM.yyyy")

var time = Utilities.formatDate(new Date(), "Europe/Berlin", "HH:mm")

Change "Europe/Berlin" to your location to get the current time. Now use the variables wherever you need them. Simple as that.

Pro tip: Try to use variables (just like shown above) for readability instead of fitting everything in one line.

Upvotes: 1

Related Questions