GAS AI BuilderApps Script Assistant Sign in

=UMUR(birth date) – age or length of service

Apps Script recipes › Sheet formulas

=UMUR(B2) gives "34 tahun 2 bulan"; =UMUR(B2; C2) counts up to another date (e.g. masa kerja until leaving).

Use it in a Google Sheet

After saving the code in a script bound to the spreadsheet, type =UMUR(A2) in a cell. It works on one cell or a range.

File Code.gs

/**
 * Years and months between two dates.
 * @param {Date} from Birth or start date, or a range of them.
 * @param {Date} [to] End date (default today).
 * @return Text like "34 tahun 2 bulan".
 * @customfunction
 */
function UMUR(from, to) {
  if (Array.isArray(from)) return from.map((row, i) => row.map((v, j) => UMUR(v, Array.isArray(to) ? to[i][j] : to)));
  if (!(from instanceof Date)) return '';
  const end = to instanceof Date ? to : new Date();
  let months = (end.getFullYear() - from.getFullYear()) * 12 + end.getMonth() - from.getMonth();
  const lastDay = new Date(end.getFullYear(), end.getMonth() + 1, 0).getDate();
  if (end.getDate() < Math.min(from.getDate(), lastDay)) months--;
  if (months < 0) return '';
  const years = Math.floor(months / 12);
  const rest = months % 12;
  return [years ? `${years} tahun` : '', rest || !years ? `${rest} bulan` : ''].filter(Boolean).join(' ');
}

Good to know

Let the AI put it in your appGAS AI Builder knows every recipe here. Describe your app and it uses this code where it fits, in a Google Apps Script project in your own Drive.

Use it in a project

More in Sheet formulas

=EMAIL_VALID(text) – check email addressesTRUE when the text looks like an email address; catches spaces, missing @ and common typos like gmial.com.=EMAIL_VALID()=HARI_KERJA_LIBUR(start; end; holidays) – working days with a holiday tabCounts working days between two dates, skipping the holidays listed in a range; =HARI_KERJA_LIBUR(B2; C2; Libur!A:A; 6) counts Saturdays as work days.=HARI_KERJA_LIBUR()=JSON_GET(json; path) – read a value from JSON in a cell=JSON_GET(A2; "customer.name") or "items.0.price" pulls one value out of JSON stored in a cell.=JSON_GET()=NOMOR_HP(phone) – one format for phone numbers"0812-3456-7890" and "+62 812 3456 7890" both become 6281234567890, the form WhatsApp links need.=NOMOR_HP()=RUPIAH(amount) – "Rp 1.250.000" as textFormats a number as rupiah text with dots between thousands, for labels, emails and documents made from the Sheet.=RUPIAH()=SLUG(text) – short ids from names"Kopi Susu Gula Aren (Large)" becomes "kopi-susu-gula-aren-large", for codes, file names and links.=SLUG()