Excel Magic Trick 1126: Populate Calendar with 'X' Based on Start, End, Weekend & Holiday Dates

preview_player
Показать описание

See how to complete a monthly employee calendar with the text "Training" based on a start and end date, and holiday and weekend dates. See the functions: IFERROR, WORKDAY.INTL, IF, AND, and NETWORKDAYS.INTL.
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:08:30

Excel Magic Trick 1126: Populate Calendar with 'X' Based on Start, End, Weekend & Holi...

Excel Magic Trick 0:18:14

Excel Magic Trick 1127: Automatically Track Budget Start, Change & Ending Balances In Excel

Excel Magic Trick 0:16:10

Excel Magic Trick 1101: Calculate MEDIAN Based On Lower/Upper Date, Exclude Holidays & Weekends

Excel Magic Trick 0:07:28

Excel Magic Trick 1099: Compare 2 Lists with semi-colon discrepancies, Excel Table For Dynamic Range

Excel Magic Trick 0:05:21

Excel Magic Trick 1119: Conditional Format Date when 44 Days Have Passed

Excel Magic Trick 0:16:08

Excel Magic Trick 1457: Text or Number Date / Times in Functions & Direct Operations (10 Example...

Excel Magic Trick 0:04:44

Excel Magic Trick 1123: Dynamic Formula To Create A List of Dates For Month

Excel Magic Trick 0:08:21

Excel Magic Trick 1125: Data Validation Custom Formula: Workday Dates in Current Month

Excel Magic Trick 0:03:48

Excel Magic Trick 1141: Link Access Table To Excel Data Source

Excel Magic Trick 0:11:57

Excel Magic Trick 1122: Repeat Row Headers Vertically For Each Day Activity Exists: Array Formula

Excel Magic Trick 0:06:05

Excel Magic Trick 1124: Conditional Format Sundays & Weekends: NETWORKDAYS.INTL & NOT functi...

Excel Magic Trick 0:09:49

Excel Magic Trick 1136: Correlation From 4 Columns: CHOOSE Array Formula “Mash” Columns Together

Excel Magic Trick 0:06:58

Excel Magic Trick 1116: Lookup Every Third Item or Lookup Every 'Nth' Item, 3 Formula Meth...

Excel Magic Trick 0:06:07

Excel Magic Trick 1138: Basic Future Value Calculation: FV Function or Math Formula

Auto-populate calendar with 0:01:32

Auto-populate calendar with an 'X' in Excel

Excel Magic Trick 0:11:03

Excel Magic Trick 1117: Calculate Average For Each Sample: AVERAGEIFS, PivotTable, or OFFSET

Excel Magic Trick 0:14:22

Excel Magic Trick 1104: Add with 6 Criteria (AND and OR Criteria) with Criteria/Data Mismatch

Excel Magic Trick 0:09:20

Excel Magic Trick 1129: Aging Accounts Receivable Reports Using Slicer, Excel Table, Page Setup

Excel 3 5 0:02:28

Excel 3 5 6 Managing Date Range Auto populating dates

Excel Monthly Calendar 0:15:58

Excel Monthly Calendar

Excel računanje broja 0:00:27

Excel računanje broja dana između dva datuma #excel #edukacija #funkcije

Dynamic Calendar in 0:08:11

Dynamic Calendar in Excel -start date & #of rows per day

Excel Magic Trick 0:05:33

Excel Magic Trick 1093: Extract Date from Middle of Description: Text To Columns or Formula?

Calendar Class: an 0:00:45

Calendar Class: an ISO Calendar in a Single Worksheet for Excel

Комментарии
Автор

Excel Magic Trick 1126: Populate Calendar with “X” Based on Start, End, Weekend & Holiday Dates
See how to complete a monthly employee calendar with the text “Training” based on a start and end date, and holiday and weekend dates. See the functions: IFERROR, WORKDAY.INTL, IF, AND, and NETWORKDAYS.INTL.

excelisfun
Автор

Brilliant guide, thank you. If Jo was doing training between 6/2-6/10 AND 6/15-6/20, how would you adjust the formula to show this on one row instead of two?

indiasebenik
Автор

Merci Monsieur Mike GIRVIN !
You are a greatest source of inspiration, and help (us) to solve so many problems even before they occur. Totally A-MA-ZING, I dare take your word ;-)
Thanks a lot

jpdavy
Автор

Would I be able to do this to only show the "training" on specific days only and not consecutive days like Mondays or Tuesdays only?

hudsonacademicaffairs
Автор

Hello Excelisfun. Thank you very much for inspiring me to create an excel calendar using excel magic trick 1126/7. May I ask, is there a way to insert the name from the name column in the formula, like replacing 'training' with a person's name. Because I want to enter the name into calendar cell under the date a person takes annual leave, like you would write a note on a calendar space? I have followed the tutorial several times and it's great, very informative and inspiring. Thank you so much

nigeltimothy
Автор

Always so many ways to do things :)
Thanks!

pmsocho
Автор

How can I automatically fill in boxes for a monthly schedule for employees who have rotating 12 hr shifts?

sandyc
Автор

Hi Mike, haven't been 'alive' for a long time.. But again great video. And inspiration for my own videos :). Keep up being creative. Cheers

ExcelxlNl
Автор

Hello, Great Video. Do you know how I can enter a date and have it show me what date would be 30 or 45 days out? Like if I enter 5/14/2018 it can auto populate what date would be 15 days and 30 days and 45 days out from the date I entered? Thank you!

yamoomoo
Автор

I am trying to create a excel sheet that auto populates due dates onto a calendar (with the the name of the project), from a pivot table. Any tips?

LuLucLucia
Автор

Nice video again. Its a gantt chart replacement

ExploringExcelAmey
Автор

Using the NETWORKDAYS.INTL with weekend parameter 11 on Excel for iOS leads to the Monday being indicated as the holiday. Any idea why that could be? I'm in Texas and fairly certain that my IPad is set up correctly?

oberghaus