Forum Discussion
Converting excel data into a printable calendar template
Thanks for the quick reply.
In my use case, I'm collecting a list of workouts related to different running races. I'm training for several races and each one has a different schedule. I'm trying to consolidate all of those in one place.
I'm attaching a spreadsheet based off your original reply.
Katrina Thompson Then it's perhaps better you go for an other solution. I while ago I tweaked a rather poorly designed calendar template to do just what you have in mind. Originally a student assignment planner, but now it's a "Berlin Marathon" planner. See attached.
Hello - I realize some time has passed since this thread. But I'm wondering if you can help with the file you created called Berlin Mara. I'm adding some columns and one of them is a time column. When I enter it at as a time it formats to a decimal. =[@team]&" (vs) "&[@Opponent]&" "&[@Time] Is there a different way I need to write the formula so that it keep the time format?
Thank you.
hooknk If I understand you correctly you should wrap the time par of your formula in the TEXT function so that you can specify the time format. Like this:
=[@team]&" (vs) "&[@Opponent]&" "& TEXT( [@Time], "hh:mm:ss" )
That worked awesome! - however is there way to not make it military time? And to include the am or pm label? - Also - this has been great. I can't thank you enough for the help.
Hi! I stumbled upon this old post looking for a solution. Seems your calendar "Berlin Mara" might work. I downloaded, however it seems the link to the "assignment" sheet no longer exists. I am assuming that's where I need to put my information? Any help would be appreciated.
Thank you for your time.
vdefilippo Yeah, the ASSIGNMENTS tab was renamed to EVENTS. You'll see a question about it a bit up in the thread.
And indeed, the EVENTS tab is where you enter whatever you want to see in the calendar.
- this is genius! It's exactly what I've been looking for. I'm pretty much a basic user of excel and I appreciate your contribution.
If you don't mind I have a few questions-
My intention is to use this as a vacation calendar. I see there is an "inconsistent formula" message in each bock. How do I fix that?
On thee week view- row 15 columns e, f, g all have "#VALUE!". How do I fix that?
Riny_van_Eekelen im jumping in this thread late, but have been reading along and this is just what i have been trying to work out. I work in a highschool and have a lot to do with calendars, have you ever created a spreadsheet that lists the events (assignments) and then can create a weekly, monthly or termly calendar but could also be filtered by faculty so they could print one?
There are quite a few steps to making the spreadsheet calendar you're looking for but it is doable. Subarrays, and filter functions. Depending on the layout you have in mind, depends on the amount of work it would take
jennyc1605 I guess it should be possible to add the faculty to this, but it would be yet another modification on top of others. It could get quite messy, I'm afraid. Do you already have the assignments in a format/structure that would fit the template you have been looking at?
- I'm looking to see if there's a way to create a time off calendar. I currently have the calendar made, tables in place for the time off from start date to end date but I'm trying to figure out how to get the data to import into a calendar. Any ideas?
Pete42320 This particular template doesn't work like that. It requires an entry for each day for it to be displayed in the calendar.
Riny_van_Eekelen hi, sorry to also bombard you with questions, but I came across this thread to discover a few Excel geniuses.
With my job, I have to coordinate more of a tentative schedule for different accounts. The time between each check in varies by the account. The variance in time between check ins can be every month, 2 months, quarter, biannual, or annually. The specific dates can vary, but it would be nice to have a guideline in place. I have to coordinate several months in advance and it sometimes involves hundreds of accounts. I'd like to create a simpler way to look at this so my coworkers and I can come up with a plan/target and still have a life. There's limited work/life balance at my job and I think this could help.
I don't want to have to plug in specific dates. I would just like the calendar to populate the data using the timeframe listed above. I can think of ways to do it manually, but I would prefer not to do that.
Like say I want account A to follow an every 2 month schedule starting in October, but I want account B to follow a quarterly schedule beginning in January. I'd like to pre-populate an approximate schedule for account A in October, December, February, etc. And account B for January, April, July, etc. I don't need specific dates. This would be more like a list format. Like for October, you should focus on confirming business with these accounts based off their preferences on frequency of contact.
Not sure if this even makes sense to anyone else. I'm trying not to divulge specific info about my company, as they are very strict.
Bonus question: say I've created an exact schedule in our CRM and I export to Excel. I'd then like to schedule communication/meetings/check ins/evaluations, etc accordingly for each account. Like this account was scheduled for October 21st. I should plan to meet with them on September 21st, then schedule three check ins in the weeks leading up to the 21st, plus a follow up the day after. But then I'd like to export that data to a calendar, preferably outlook. Is there a way to get Excel to generate these touch points automatically? I basically have to plan two calendars because our CRM system does not schedule out these touch points.
I guess the better question is whether I can use Excel to create my own CRM because ours is terrible, and can I then export that data to Outlook or another calendar.
Riny_van_Eekelen Hello, thank you so much for the Berlin Marathon Planner, which I'm trying to use for a list of mine. I'm trying to change the starting calendar day to Monday instead of Sunday. Unfortunately it doesn't work for me, can anybody help me? The starting excel file I'm using is the Berlin Mara which was uploaded to this discussion.
CBRWPA Apart from just changing the headers, that requires a small modification to the formulas in row 6 of the Month view and (hidden) row 5 of the Week view. These use the WEEKDAY function to calculate the week day for the start of the month. The default is based on Sunday-Saturday. Adding the optional return_type number 2 will use Monday-Sunday.
See attached.
Realizing this is an old thread, this is exactly what I'm looking for. I have downloaded your Berlin Mara v2 file. Once I enable the protected view, the formula no longer works. I'm assuming because it's a different version of excel that I'm using? I have Professional 2016. Any tips to get this working for me? appreciate your time!
Hi Riny,
This is great exactly what i'm looking for
I have copy and pasted the 'month view' to my 'Actions Tracker' spreadsheet but am struggling to adapt the formula to pull events into the month view
I believe changing the array is what will do it but need some pointers if you can help with thisThank you
Azaam_Emambacus Not sure what you are trying to achieve, but if you are taking the Month View sheet and copy/paste it into another workbook, you would have to structure the event list (table name, column headers etc.) in that new workbook the same as in the file I uploaded earlier. It might work, but I can't guarantee it. It's not something I designed myself. I merely adjusted an otherwise totally useless template that's offered on-line.
Using the Berlin calendar as an examples can you have a start date and end date that would report that event each day during the time frame or would each event need their own data entry?
And thanks your answers have so far been very helpful
Riny_van_Eekelen thank you for the "Berlin Marathon" planner I was able to customize it for exactly what I need. Only thing how can I get Monday to be the start date and not Sunday? Any help is so appreciated!
RM2568 This may be too late for you, but to start the week on a Monday not a Sunday change as below:
Original - Week Start Sunday
=IF(WEEKDAY(DATEVALUE("1-"&WkMonth&"-"&WkYear))=COLUMN(A$2),1,IF(LEN(A5)>0,A5+1,""))
New - Week Start Monday
=IF(WEEKDAY(DATEVALUE("1-"&WkMonth&"-"&WkYear),2)=COLUMN(A$2),1,IF(LEN(A10)>0,A10+1,""))
Katrina Thompson Correct. Believe this one is easier, because you want the monthly view.
Riny_van_Eekelen Thank you for this. I have modified it for my use, but am just wondering if you could share any tips on how you created it. Is it all excel filters, or does it use any power queries etc? As I would like to understand it a bit better in case I need to make further edits.
