Forum Discussion

Mel_Sharpe's avatar
Mel_Sharpe
Copper Contributor
Nov 16, 2023

Inserting data from a separate spreadsheet into Calendar

Hi, I have an excel sheet which shows the dates, type and duration of my courses and I want to use this information to populate a calendar that I have made (will be on a separate excel document)  for example

 

Column A     Column B            Column C      Column D

type              Start Date            End Date      Duration

course             01/10/2024       05/10/2024        5

Conference      01/02/2024      03/02/2024         3

 

I want the formula to check the date on the calendar and if it matches the start date to populate that with the type of the course and then copy this information in each date for the duration so from 1/10/2024 and for the next 4 days (so 5 days in total are marked as course on the calendar)

Is this possible? 

 

Please help this is driving me mad

 

excel
Formulas and Functions

2 Replies

Sort By
  • rachel's avatar
    rachel
    Steel Contributor
    Nov 18, 2023

    Mel_Sharpe 

     

    Hi 

     

    I use below formula to check whether each date in "Calendar" is between the Start Date and End Date of the course, and if it is, I will mark it with the course name.

     (I attached an example spreadsheet, hope it helps).

     

    =LET(courseInfo, XLOOKUP(C$1,CourseData!$A:$A,CourseData!$B:$D,"",0,1), startDate, INDEX(courseInfo,1,1),endDate, INDEX(courseInfo, 1, 2),IF(AND($A2>=startDate, $A2<=endDate), C$1, ""))

     

     

Resources