+ Reply to Thread
Results 1 to 4 of 4

Repeating/Recurring Entries into database with option to view in Calendar view.

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    45

    Repeating/Recurring Entries into database with option to view in Calendar view.

    I am looking to see if there is a way to automatically add repeating/recurring entries to a database in Excel. For example, currently we use a calendar (calendarwiz) to enter and track jobs that come in, not time specific, but location/stop sequence order, we select all day (this isn't important as we manage this in a map app), but 90% of our jobs are recurring, either weekly, twice weekly, two weekly, 4 weekly, 3 monthly etc). The calendars can manage this, with the option of altering the repeating series, or the single event without changing the series. but I can not find a database that can do this, so trying to build one in excel, if possible. At the moment each week I import the calendar into excel and manipulate the data into columns, like customer, asset number, completed etc... Trying to eliminate all the multiple data handling.

    And to add an extra headache, I would like to be able to display the database into a calendar format, for the field guys to be able to look at in a glance. I could probably have a hide column that combines multiple columns to populate the calendar with the job details. Is this possible? Pivot table that returns text values would be nice, but from what I can find, it is not possible.

    And just to add to the headache, something that can be viewed in mobile app, or Office 365 online.

    Hope I'm not asking too much.

    Thank you

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Repeating/Recurring Entries into database with option to view in Calendar view.

    I have submitted several calendar files on this forum, which basically use an Events sheet which lists activities and dates, and a Calendar sheet, which usually shows a single month (selectable from a drop-down) with multiple events for each day. The files are formula-driven, so should (and do) work with various versions of Excel.

    The simplest Events sheet just has a Title and (single) date for each event, but I have also taken this further for events which span several days by having a start date and an end date for each event, and then in a separate table the event is expanded automatically to have a single record for each day, and this table supplies the data for the calendar view.

    It strikes me that a similar approach could be taken in your case, so that the basic raw data could be expanded according to another field which defines "frequency" of event. For a twice-weekly event you would also need to define which days the event occurs on, and for each frequency type you may still need an end-date so you can determine how long the expansion of data should cover.

    You will need to put together a sample file which shows examples of each type and makes it clear what you expect to see on the calendar. The yellow banner at the top of the screen gives details of how you can attach an Excel file to one of your posts (a picture is no use to us). I'll comment further when I see your file.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    45

    Re: Repeating/Recurring Entries into database with option to view in Calendar view.

    Thank you Pete,

    Actually, I am thinking that I may not need a calendar view. But more importantly a way to automatically enter recurring events into an excel database. We service septics, portables and bulk tanks at different locations for different clients. Most jobs are of a recurring nature, for instance portable toilets at building sites are on a schedule of between 1 to 4 weeks until the end of the building project. I would like to be able to enter a job from Client???? at xxxx address on a schedule of say 4 weekly and have it automatically populate the spreadsheet with each recurring date. The calendars do this, but there is a maximum end date of two years so it doesn't go on forever and ever, understandably. Then each day we can filter out jobs for that day and assign it to a particular operator.

    I hope that makes sense.
    Last edited by mariannehislop; 03-25-2021 at 07:40 PM.

  4. #4
    Registered User
    Join Date
    09-24-2014
    Location
    Pinjarra, Australia
    MS-Off Ver
    2011
    Posts
    45

    Re: Repeating/Recurring Entries into database based on dates.

    So I've been brain storming all night. Wondering if something like a Loop Until macro would work.

    Each job would have a Recurring Job ID linking all the records, just like the calendars do. Say John Smith calls up and books a job in for a service to happen once a week until notified otherwise. Enter the first commencement date, and an end date which would have to be like the calendars, max end date of say two years, then the macro will copy and past a record until the service date is => than the end date, then stop. I've done some basic VBA coding in the past, usually tweaking a recorded Macro. How would I write code for this?

    I can use IF formulas to work out how to calculate the next due date based on DAY/WEEK/MONTH criteria. I need to figure out how to write a formula if more than one specific day per week is needed, say every Tuesday and Thursday.

    Is this possible?


    Once this works then I can deal with how to get it into a calendar view.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-27-2021, 01:48 PM
  2. [SOLVED] Campaign plan with two tabs - content view AND date view
    By JoanneBeale in forum Excel General
    Replies: 15
    Last Post: 10-20-2018, 03:22 AM
  3. Return to worksheet from backstage view, i.e. close backstage view or exit backstage view
    By freddyroosevelt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2018, 12:44 AM
  4. Word Opens in DRAFT VIEW. How to open in PRINT LAYOUT view
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2017, 06:47 PM
  5. Replies: 3
    Last Post: 08-31-2012, 12:40 PM
  6. [SOLVED] Issue - replies/view count labels in New Posts view
    By teylyn in forum Suggestions for Improvement
    Replies: 5
    Last Post: 03-16-2012, 05:45 AM
  7. [SOLVED] what is view option
    By brian in forum Excel General
    Replies: 1
    Last Post: 03-30-2006, 07:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1