Calendar Data to Input to Excel like Outlooks built in scheduling form

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
20
Hey There is there a way I can create something similar to the outlook Calendar input form that I can use for budgeting analysis on my excel spreadsheet?

Is it crazy hard?
Would this need to be done with User forms?
Is there an alternate method you would suggest?

Thanks in advance!

Kind regards
Dizzy
 

Attachments

  • CalendarInput- Yearly.png
    CalendarInput- Yearly.png
    20.8 KB · Views: 8
  • CalendarInput- Monthly.png
    CalendarInput- Monthly.png
    20 KB · Views: 8
  • CalendarInput- Weekly.png
    CalendarInput- Weekly.png
    19.8 KB · Views: 6
  • CalendarInput- Daily.png
    CalendarInput- Daily.png
    18.4 KB · Views: 8

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Hi Dizzy,

Its not crazy hard, the thing you'll find hardest will be processing all the payment date data once you click OK but this can be done by seperate functions
Do you know how to code in VBA?
 

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
20
Hi Dizzy,

Its not crazy hard, the thing you'll find hardest will be processing all the payment date data once you click OK but this can be done by separate functions
Do you know how to code in VBA?
Yes I am all across the VBA I need to process the Date Data, once I have a list generated based on the conditions I apply.

All I need right now is a way to enter what ever financial variations I may need eg
-Monthly insurances are always 17th of each month except weekends and public holidays
- End of month accounts
- Quarterly (3 monthly) Gas & electricity
- Yearly Car registration
- Weekly income
- Fortnightly income

that's just to name a few There are countless iterations that may be required and the Outlook Form has every option required.

Note: I already have a Yearly calendar made up to use as a data source.
I've done a tonne of time on this already and wondering if there is a better way

so how can I create a form similar to the previously attached images but in excel so that it can spool out all the dates that meet the required criteria


Thanks Again! Dizzy

PS. I am not comfortable uploading the actual excel sheets, as on other pages has highly confidential data within it and it's not worth the hassle to do so because I have tonnes of 'Defined Names' and Linked cells to another tab call 'Data Source' etc etc etc.
Short answer is, it's just a hassle to upload the Worksheet.
Also, it is because all I am after is a direction of where I can go to next
 

Attachments

  • Calendar Data source - Yearly.png
    Calendar Data source - Yearly.png
    107.4 KB · Views: 3

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Looks like you need to create a few functions to handle what to do with a date and a frequency

Have you thought of a select case statment?

Code:
Function NextDate(CurrentDate As Date, Freq As String) As Date
    Select Case Freq
        Case Is = "Monthly"
            NextDate = DateAdd("m", 1, CurrentDate)
            
        Case Is = "Weekly"
            NextDate = DateAdd("d", 7, CurrentDate)
        
        Case Is = "Yearly"
            NextDate = DateAdd("y", 1, CurrentDate)
            
    End Select
End Function

so on and so forth
of course you'll have to do extra checks when the date comes back from the function to see if it a weekend and change it accordingly but your could loop through as many iterations up to the end date as you like and write the data to sheet?
 

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
20

ADVERTISEMENT

Th
Looks like you need to create a few functions to handle what to do with a date and a frequency

Have you thought of a select case statment?

Code:
Function NextDate(CurrentDate As Date, Freq As String) As Date
    Select Case Freq
        Case Is = "Monthly"
            NextDate = DateAdd("m", 1, CurrentDate)
           
        Case Is = "Weekly"
            NextDate = DateAdd("d", 7, CurrentDate)
       
        Case Is = "Yearly"
            NextDate = DateAdd("y", 1, CurrentDate)
           
    End Select
End Function

so on and so forth
of course you'll have to do extra checks when the date comes back from the function to see if it a weekend and change it accordingly but your could loop through as many iterations up to the end date as you like and write the data to sheet?
is seems like an ok answer but that’s only going to work for the ‘current date’ + (x) formulas
Which I have already written that formula and that was the easy one lol

This will not work if I need a payment processed the first Tuesday of every month (which I do have one of those also) and I have also written the formula for that too.

inwas just hoping for another solution as this has taken 40 hours to make so far and I keep finding errors occasionally
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Hi Dizzy
I understand you would like an easy answer and hoped there was oe out there
Reality is, in this situation your choices are to build it yourself or purchase a ready made solution.
Don't give up, you will finish this but I think your question is too broad, most of the time on these sites your question will be answered if you break it down into smaller pieces and post each piece.
I have given you a small piece of the puzzle and below is another to answer your second question, but these things arn't always simple, you get the satisfaction of a job well done when you put in the work.

Code:
Sub createRec()
    Dim startcell As Range
    Set startcell = Sheet1.Range("A1")
    
    For i = 1 To 20 'number of iterations you have predefined
        startcell = "Expense Name"
        startcell.Offset(, 1) = NextDate(UserDefDate, UserDefFreq) ' so if its from a form or somewhere else in code
        startcell.Offset(, 2) = "Debit"
    Next i
End Sub
 

HeyItsDizzy

New Member
Joined
Apr 11, 2018
Messages
20
Hi Dizzy
I understand you would like an easy answer and hoped there was oe out there
Reality is, in this situation your choices are to build it yourself or purchase a ready made solution.
Don't give up, you will finish this but I think your question is too broad, most of the time on these sites your question will be answered if you break it down into smaller pieces and post each piece.
I have given you a small piece of the puzzle and below is another to answer your second question, but these things arn't always simple, you get the satisfaction of a job well done when you put in the work.

Code:
Sub createRec()
    Dim startcell As Range
    Set startcell = Sheet1.Range("A1")
   
    For i = 1 To 20 'number of iterations you have predefined
        startcell = "Expense Name"
        startcell.Offset(, 1) = NextDate(UserDefDate, UserDefFreq) ' so if its from a form or somewhere else in code
        startcell.Offset(, 2) = "Debit"
    Next i
End Sub
Awesome.
Thanks, EFAN

I didn't think there would be an easy solution, I just wanted to make sure I was on the right path or if there was a better one I should pursue.

I think I am on the right path, I just foresee a 25+ lined equation in my near future, as I need to make sure I used named ranges and keep the whole thing dynamic to avoid creating error by a simple mistype.

I think when I Build one I will sell it! because I can't find it anywhere out there.

Once Completed I'll post a link to the Completed Calendar input file here. as mentioned this has taken over 40 hours so far and I estimate another 80 hours required if not more.
So if I can save a few people 120 hours of work, then I will do the best I can!


If ar reader is reading this now and there is no link to this file here send me a private message on this board and I will give you an ETA on when I think it will be finished
 

Watch MrExcel Video

Forum statistics

Threads
1,126,981
Messages
5,621,956
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top