How to add holidays and time off from Google Sheets to Google Calendar

The short answer

To add holidays from Google Sheets to Google Calendar, put one event on each row with a Title and Start date. For a multi-day absence, add an End date containing the last day off. Choose a calendar you can edit in CalendarRows, check the column mapping, click Preview and send the rows. A date-only row becomes an all-day event unless a mapped All day column says otherwise.

CalendarRows Free covers all-day rows, end dates, colours and guests. It allows 50 calendar changes per day, and each event created, updated or deleted counts as one change. A Repeat value such as yearly requires Pro; you do not need Pro just to make an event span several days.

A CSV import is enough for a one-time list that you do not expect to update from the sheet. If you are comfortable running code, the longer free Apps Script in our create-events guide supports all-day events too. CalendarRows is useful when you want a preview and expect to edit or delete linked events from the spreadsheet.

Before sending a vacation tracker, decide which entries are ready to appear on the team calendar. A spreadsheet can be your working list, but sending is a separate action: review the dates, the selected rows and the destination before you make changes to the calendar.

Set up the time-off sheet

Use one row per holiday or absence under a header row. Only Title and Start date are required by CalendarRows. Add End date for an absence lasting more than one day, and use optional columns for the details your team needs. This example mixes all-day and timed rows, so it includes an All day column to make the intended type explicit.

The first row is a one-day holiday: its End date is blank. The second covers Monday through Friday, including both December 7 and December 11. The third is a timed absence on one day. Its end time is later than its start time, so it does not need a separate End date.

Location and Description are free-text fields. Leave them empty when they do not help people understand the event. You can also add a Guests column if particular email addresses need to be included. CalendarRows accepts up to 50 addresses per event, separated by commas, semicolons or spaces. It adds guests without sending invitation emails, so arrange any needed notice separately.

Keep the title and description appropriate for the people who can see the calendar. A general title such as Out of office may be enough for a team calendar. Decide what information belongs in the sheet and calendar before copying details from a private leave request.

CalendarRows suggests mappings from your headers, but check each one. A column can be used for only one field. Make sure the Start date, End date and any time columns point to the intended cells, particularly if your tracker already has other dates such as a request date or approval date.

Choose all-day or timed events

Without a mapped All day column, a row with a date and no time anywhere becomes an all-day event. That means no start time and no end time, whether the time is in a separate column or in a date cell. A multi-day all-day event uses Start date for the first day and End date for the last day.

If you map an All day column, that column decides which rows are all-day. Use TRUE or FALSE, yes or no, or a checkbox. For the example above, the first two rows are TRUE and the half-day row is FALSE. Check the values on every row when you add the column to an existing tracker.

End date is inclusive for CalendarRows all-day events. Enter the final day that should appear on the calendar, not the following day. For example, Start 2026-12-30 and End 2027-01-01 cover December 30, December 31 and January 1. A blank End date creates a one-day event. The CalendarRows documentation describes the End date for multi-day all-day events.

For a timed absence, enter the intended start and end times. If a timed row has no start time, CalendarRows uses 9:00 AM and adds a note to the status. If it has no end time, the default length chosen in the sidebar applies: the default is one hour, and you can choose from 30 minutes to eight hours. Explicit times make a half-day entry easier to review.

Times use the spreadsheet’s time zone, found under File → Settings in Google Sheets. CalendarRows sends timed events with that zone attached. A viewer may see the time according to their own calendar settings, so check those settings when comparing what different people see. After a test send, open the event and verify its date and time.

For an overnight timed absence, an end time earlier than or equal to the start time needs an End date on the next day. Otherwise the row shows End is not after the start. An all-day row whose End date is before its Start date also produces an error. Correct the row and preview it again.

Build titles and end dates

A formula can turn a name or other label into a consistent event title. If A2 contains Facilities, put this formula in the Title cell for that row:

=A2&" - out of office"

The result is Facilities - out of office. Map the formula’s column to Title, rather than mapping the source label as well. Check the displayed result before sending, especially if the title will be visible to a group.

You can calculate an inclusive End date from a start date and a number of working days. Put the start date in B2, a positive whole number of working days in C2, and this formula in the End date cell:

=WORKDAY(B2,C2-1)

WORKDAY skips Saturdays and Sundays. This example assumes the start date itself is a working day and counts it as day one. If B2 is Monday 2026-12-07 and C2 is 5, the formula returns Friday 2026-12-11. CalendarRows treats that Friday as the last day of the all-day event.

To exclude dates in a holiday range as well, supply that range as the optional third argument. For example, =WORKDAY(B2,C2-1,$H$2:$H$10) uses the dates in H2:H10 as holidays. Check that the start date is itself a working day and is not in the holiday range, then review the resulting end date. The formula counts working days; a single multi-day calendar event still covers the intervening calendar days.

If the number means consecutive calendar days instead, use =B2+C2-1. With December 7 in B2 and 5 in C2, this also returns December 11. If the period crosses a weekend, the two formulas can produce different end dates. Choose the calculation that matches the request, and format the result as a date so it is easy to inspect.

Use actual date cells for arithmetic, not text that merely looks like a date. If a result is unexpected, check the start date, the count and any holiday range before sending. The formula supplies a date; it does not decide whether the requested time off has been approved.

Send rows to a team calendar

Create or pick a calendar in Google Calendar that you can edit for the team’s holidays and time off. CalendarRows lists calendars where you can edit events. If the calendar you want is missing, check your access in your account or with its owner before sending. Choose the destination deliberately, particularly if you also maintain a personal calendar.

1. Open the spreadsheet with one event per row and choose Extensions → CalendarRows → Open CalendarRows.
2. On To Calendar, select the sheet, header row and team calendar. Check the suggested column mappings, including All day, End date and any time columns you use.
3. Choose all rows or selected rows. Click Preview and review the create, update, delete and unchanged counts, along with any rows reported as having problems.
4. Click Send to Calendar when the preview matches your intention. Check the progress and row statuses, then inspect a few events in the destination calendar.

Preview does not change the calendar or use a calendar change. A small first batch can help you confirm that the sheet’s dates and event types match what you intend. Check a one-day holiday, a multi-day vacation and a timed absence if your sheet includes all three.

Use a mapped Color column to distinguish types of time off. CalendarRows accepts the names Lavender, Sage, Grape, Flamingo, Banana, Tangerine, Peacock, Graphite, Blueberry, Basil and Tomato, or numbers 1 to 11. Agree on meanings your team can remember, then check the mapping and spelling. Our colour-coding guide walks through the field in more detail.

The first send adds CalendarRows event ID and CalendarRows status columns. Keep those columns and their notes with the rows. They identify the associated events and help CalendarRows recognise changes. Do not copy an existing event ID into a new holiday row.

Use CSV or Apps Script for free

For a fixed, one-time list, a CSV can be the simpler choice. Prepare English headers including Subject, Start Date, End Date and All Day Event. Only Subject and Start Date are required. For an all-day row, the import guide uses True for All Day Event and leaves the time fields empty. Follow the spreadsheet and CSV import guide for date formatting, a test import and the import steps.

CSV does not keep a link between a spreadsheet row and its event. Importing a corrected file adds events again rather than updating the first set. It also has no guest, colour or repeat column. If the list is likely to change after publication, account for that limitation before choosing CSV.

The free Apps Script in our create-events guide is another option. Its longer version supports an All day checkbox, multi-day events, guests and colours, and saves an event ID so later runs can update the event. It requires you to paste and run code in your spreadsheet. Guests in that script are set when an event is first created; later guest changes need to be made in Calendar.

The basic script in the same guide handles ordinary timed events and basic updates, but all-day events need the longer version or more code. In the basic script, deleting a row does not delete its event. Read the guide’s limitations and test a small set before relying on either version for a team schedule.

CalendarRows is a better fit when you want to preview a batch, edit linked rows and use an Action column for deletions. The free plan may still be enough for a regularly maintained PTO calendar from a spreadsheet if its daily change allowance fits your workload.

Handle approvals, changes and limits

Keep requests that are still being considered separate from the rows you intend to send, or select only the approved rows when you preview. CalendarRows offers all-row and selected-row sending; use the selection and Preview to check the actual batch. The add-on sends the rows you choose, so an approval decision needs to be made before the send.

To change an event already sent with CalendarRows, edit its existing row, keep its event ID and status columns and notes, and select the same calendar. Preview, then send again. Only changed rows are sent once their links and fingerprints are established. For example, if a five-day vacation becomes four days, change the End date from Friday to Thursday and check that Preview shows the expected update. See the update guide for more on linked rows.

To remove an event, type delete in a mapped Action column on its row. Select the calendar where the event was created, preview the deletion and send. The status becomes DELETED. Deleting a row from the sheet is not the documented deletion workflow; use the Action column so the event can be identified. The bulk-delete guide covers reviewing a larger deletion.

Free allows 50 changes per day, with each create, update or delete counting as one. Previews and unchanged rows do not use changes. If you have the full allowance and send 60 new holiday rows successfully, 50 creates use the first day’s allowance and 10 remain. CalendarRows saves its place and pauses; click Resume after the allowance resets at 00:00 UTC. Changes already used that day reduce what is available for the batch.

Pro allows 2,000 changes per day, subject to fair use. For a list that only occasionally exceeds 50 changes, resuming after the reset may be enough. If the team regularly needs larger batches, compare the options on the pricing page.

If you send linked rows to a different calendar, CalendarRows creates new events there and the row IDs then point to those new events. The original events are not moved or removed. Keep the same destination when you mean to update or delete the originals, and investigate an unexpected create count before sending.

Plan yearly dates

Some dates may be suitable for a yearly repeating event. With CalendarRows Pro, map a Repeat column and enter yearly for such a row. Repeat values on Free are not sent and produce an error. A multi-day event does not itself require Repeat or Pro: use Start date and inclusive End date for a single vacation period.

Holiday dates can differ by year, so check each year’s actual date rather than assuming the same date always applies. Separate rows make those dates explicit and work on Free without a Repeat value. They also let you review the title and dates for each year before sending. A CSV has no repeat column, so it likewise needs one row per occurrence.

Use the recurring-events guide when a true repeating series is what you need. For a list of individual dates, leave Repeat unmapped or empty and preview the rows as separate events.

Common problems

An all-day event ends a day early or late. For CalendarRows, End date is the last day included. Check that the mapped End date contains the final day off, not the day after it. If a formula supplies that date, check whether its count means working days or calendar days. Edit the linked row, select the same calendar, preview and send the correction.

An event shows as timed at 9:00 AM. Check whether All day is mapped and what its value says for that row. A mapped column controls the event type. Also inspect both date and time cells for a time you did not intend. A timed row without a start time uses 9:00 AM and receives a status note. Correct the row and verify the updated event.

The status says End date is before the start date. Compare the two dates and their column mappings. Check the year as well as the month and day, and inspect any formula that calculates End date. Enter the intended last day, then preview and send again.

Events appear in another calendar as well. Sending linked rows to another calendar creates new events there; it does not move or delete the originals. The row IDs then point to the new events. Check which calendar you selected and inspect both calendars before deciding how to correct the list. Use the original calendar for updates to its events.

The calendar is missing from the picker. CalendarRows lists calendars where you can edit events. Check your access in your account or ask the calendar owner to check it. Do not send to a different calendar merely because it is listed.

A run pauses or a row has an error. Read the progress card and status. If the daily allowance is exhausted, click Resume after 00:00 UTC. If a row says PENDING, an ID was reserved before the event was confirmed; send again to finish, and CalendarRows will not make a duplicate for that pending row. For ERROR, fix the stated problem and send again.

The time looks different to a viewer. Check the spreadsheet time zone under File → Settings and check the viewer’s calendar settings. For timed events, compare the intended local time with the event after sending. All-day rows should also be checked for an unintended time or an incorrect All day value.

Frequently asked questions

Related

• Create Google Calendar events from Google Sheets
• Import a spreadsheet or CSV into Google Calendar
• Update Google Calendar events from Google Sheets
• Bulk delete Google Calendar events
• Put a shift schedule in Google Calendar
• CalendarRows documentation
• All guides

Disclosure: we make CalendarRows, the Google Sheets add-on mentioned in this guide. CSV import works for a one-time list without the add-on, and the longer Apps Script in our create-events guide supports all-day events without it.

Google Sheets, Google Calendar and Google Workspace are trademarks of Google LLC. CalendarRows and Cellbrook are not affiliated with or endorsed by Google.