Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-14-2014, 01:04 PM
patidallas22 patidallas22 is offline Excel to auto populate dates based on start and end Windows XP Excel to auto populate dates based on start and end Office 2007
Novice
Excel to auto populate dates based on start and end
 
Join Date: Mar 2012
Posts: 12
patidallas22 is on a distinguished road
Default Excel to auto populate dates based on start and end

I would like to create a template using an excel spreadsheet where we can calculate monthly project costs.

1. I have provided the start date and the end date of the project.
2. I have provided the formula for the monthly cost.

I would like Excel to auto populate the end of month date beginning with my start date (by row) and to automatically stop when it reaches the end date.

How can this be done? Does this need to be a macro?

Thanks,


Pati Doherty
Reply With Quote
  #2  
Old 04-14-2014, 10:34 PM
macropod's Avatar
macropod macropod is offline Excel to auto populate dates based on start and end Windows 7 32bit Excel to auto populate dates based on start and end Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It could be done via formulae, provided you were prepared to tie up however many rows the maximum project length is; otherwise a macro would be required. An alternative is to input the first two months' dates & amounts in A1:A2 & B1:B2, then select the lot and drag down as far as needed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-15-2014, 08:48 AM
patidallas22 patidallas22 is offline Excel to auto populate dates based on start and end Windows XP Excel to auto populate dates based on start and end Office 2007
Novice
Excel to auto populate dates based on start and end
 
Join Date: Mar 2012
Posts: 12
patidallas22 is on a distinguished road
Default

Thanks for the response, but I think I do want to create a macro. I want the macro to auto-fill the end of month dates in rows. I would like to provide a start and end date and then have the macro automatically fill the number of rows needed based on a monthly end of month date. I started a spreadsheet and created an "Input" tab that would keep my variables and then for now I manually copied down my months to show how I would ultimately want it to look. Any help with the macro (including a button for someone to push) would be great! I've attached my spreadsheet I have started with.
Attached Files
File Type: xlsx Operation template.xlsx (52.2 KB, 18 views)
Reply With Quote
  #4  
Old 04-15-2014, 04:39 PM
macropod's Avatar
macropod macropod is offline Excel to auto populate dates based on start and end Windows 7 32bit Excel to auto populate dates based on start and end Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Your original post refers to:
1. the start date and the end date of the project.
2. the formula for the monthly cost.
Your attached workbook shows not only (1) but numerous other dates as well and no indication of (2). I assume (2) is one of A, B, C, D or V, but I can't tell which.

It is also not apparent where the output is to go. Your O&M sheet only lists annual periods, not the monthly periods you mentioned.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-16-2014, 06:31 AM
patidallas22 patidallas22 is offline Excel to auto populate dates based on start and end Windows XP Excel to auto populate dates based on start and end Office 2007
Novice
Excel to auto populate dates based on start and end
 
Join Date: Mar 2012
Posts: 12
patidallas22 is on a distinguished road
Default

I manually populated the spreadsheet by dragging the formula to the bottom. In this instance, I would use the Construction Start Date and the Operations End Date, but either way I don't know how to build a macro to populate down a spreadsheet with dates like the example I provided. Sorry if the other fields added confusion, don't worry about the other fields, I am only interested in start and end populating down in rows and auto stopping when it reaches the Operations End Date. Thanks!
Reply With Quote
  #6  
Old 04-16-2014, 07:11 PM
macropod's Avatar
macropod macropod is offline Excel to auto populate dates based on start and end Windows 7 32bit Excel to auto populate dates based on start and end Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That's all very well, but your interval dates are based on years, not months. So which is it to be?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 04-17-2014, 06:30 AM
patidallas22 patidallas22 is offline Excel to auto populate dates based on start and end Windows XP Excel to auto populate dates based on start and end Office 2007
Novice
Excel to auto populate dates based on start and end
 
Join Date: Mar 2012
Posts: 12
patidallas22 is on a distinguished road
Default

Let's use a start date of January 1, 2014 and an end date of 12/31/2048. Now let's show the last month of the year, one row for each year until it stops automatically in 2048. Thank you!
Reply With Quote
  #8  
Old 04-17-2014, 04:06 PM
macropod's Avatar
macropod macropod is offline Excel to auto populate dates based on start and end Windows 7 32bit Excel to auto populate dates based on start and end Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try adding the following macro to your 'Inputs' worksheet's code module:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim StartDate As Date, Duration As Long, i As Long
With ActiveSheet
  If Intersect(Target, .Range("D9:D11")) Is Nothing Then Exit Sub
  If ActiveSheet.Range("D9") = "" Then Exit Sub
  If ActiveSheet.Range("D10") < 1 Then Exit Sub
  StartDate = .Range("D9").Value
  Duration = .Range("D11").Value
End With
With Worksheets("O&M")
  .Range("A5:D" & .UsedRange.Rows.Count).ClearContents
  .Range("A5").Value = StartDate
  .Range("B5").Value = DateAdd("yyyy", 1, StartDate) - 1
  .Range("C" & i + 5).Value = Format(StartDate, "yyyy")
  .Range("D" & i + 5).Value = 1
  For i = 1 To Duration - 1
    .Range("A" & i + 5).Value = DateAdd("yyyy", i, StartDate)
    .Range("B" & i + 5).Value = DateAdd("yyyy", i + 1, StartDate) - 1
    .Range("C" & i + 5).Value = Format(DateAdd("yyyy", i, StartDate), "yyyy")
    .Range("D" & i + 5).Value = i + 1
  Next
End With
End Sub
The macro will run automatically any time you select a cell in the D9-D11 range.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Last edited by macropod; 04-17-2014 at 04:11 PM. Reason: Code revision
Reply With Quote
  #9  
Old 04-21-2014, 08:47 AM
patidallas22 patidallas22 is offline Excel to auto populate dates based on start and end Windows XP Excel to auto populate dates based on start and end Office 2007
Novice
Excel to auto populate dates based on start and end
 
Join Date: Mar 2012
Posts: 12
patidallas22 is on a distinguished road
Default

Thanks for your help, this is what I believe I need. Since I'm only just learning about macros, I am getting a debug error and not sure how to fix it. I've attached the new spreadsheet with the macro and also a picture of the error. Thanks again!
Attached Images
File Type: jpg Debug error.jpg (207.8 KB, 25 views)
Attached Files
File Type: xlsm Operation template.xlsm (59.6 KB, 10 views)
Reply With Quote
  #10  
Old 04-21-2014, 03:14 PM
macropod's Avatar
macropod macropod is offline Excel to auto populate dates based on start and end Windows 7 32bit Excel to auto populate dates based on start and end Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Two problems:
1. You didn't add the macro to your 'Inputs' worksheet's code module. Instead, you created a new module and added it to that.
2. You renamed the macro, from 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' to 'Sub AutoDate()'.
Both actions prevent the macro running automatically.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to auto populate dates based on start and end Auto Populate Word From to Excel File webber Word 1 10-02-2013 02:52 PM
Start / End Dates in conjunction with man days loobs40 Project 5 09-19-2013 09:21 AM
how to populate textbox based on combobox selection in word IvanGeorgiev Word 1 02-21-2013 07:32 PM
Excel to auto populate dates based on start and end how to populate textbox based on combobox selection in word IvanGeorgiev Word VBA 1 02-21-2013 07:28 PM
Excel to auto populate dates based on start and end Task Start and End Dates OTPM Project 2 12-04-2011 09:14 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft