+ Reply to Thread
Results 1 to 11 of 11

Calc days in month when full and variable month for a budget report

  1. 08-08-2018, 09:24 AM #1
    JOHNAK27
    JOHNAK27 is offline
    Registered User
    Join Date
    04-14-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    23

    Calc days in month when full and variable month for a budget report

    Hi all

    I need to calculate a client's budget monthly over a set period of time. The report may be run for a set of months or single month.

    The calculation will need to consider when full month days and when variable, that is, first month and last month of client's budget.
    I have attached sample, with partial formula, any assistance in completing would be appreciated.



    Many thanks
    John
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-08-2018, 09:32 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,146

    Re: Calc days in month when full and variable month for a budget report

    OK, so where is the attempt at the report with partial formulae? I don't see it in the workbook.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-08-2018, 09:54 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,146

    Re: Calc days in month when full and variable month for a budget report

    Sorry - I see it now - you want us to help you to improve the formula for calculating the monthly amounts - is that it?
    Reply With QuoteRegister To Reply
  4. 08-08-2018, 10:00 AM #4
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calc days in month when full and variable month for a budget report

    Try this formula in F3 and copy foward

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 08-09-2018, 08:33 AM #5
    JOHNAK27
    JOHNAK27 is offline
    Registered User
    Join Date
    04-14-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Calc days in month when full and variable month for a budget report

    Hi Jose
    Formula worked perfectly until the last month, it counted days from end date to end of month, instead of other way round, start of last month to end date.
    It should be 21 days remaining not 9.
    Tried to play around with it, but couldn't work it out.
    If you can help.
    Thanks
    John
    Reply With QuoteRegister To Reply
  6. 08-09-2018, 08:34 AM #6
    JOHNAK27
    JOHNAK27 is offline
    Registered User
    Join Date
    04-14-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Calc days in month when full and variable month for a budget report

    Yes thank you.
    Reply With QuoteRegister To Reply
  7. 08-09-2018, 08:42 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    83,146

    Re: Calc days in month when full and variable month for a budget report

    If you could please add a more representative workbook, then one of us can adapt José's formula to fit.
    Reply With QuoteRegister To Reply
  8. 08-09-2018, 09:59 AM #8
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calc days in month when full and variable month for a budget report

    Hi

    Try this update in F3

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by José Augusto; 08-09-2018 at 10:03 AM.
    Reply With QuoteRegister To Reply
  9. 08-09-2018, 10:32 AM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,430

    Re: Calc days in month when full and variable month for a budget report

    Try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and I get 20 days for the last month. Also 27 days for the first month ((31-5)+1)
    Dave
    Reply With QuoteRegister To Reply
  10. 08-10-2018, 09:01 AM #10
    JOHNAK27
    JOHNAK27 is offline
    Registered User
    Join Date
    04-14-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Calc days in month when full and variable month for a budget report

    Jose,
    the 2nd formula worked once I added 1 day to first month and minus 1 day on last month.
    =$E3*IF(MONTH(F$2)>MONTH($C3),0,IF(MONTH($B3)=MONTH(F$2),F$2-B3+1,IF(MONTH($C3)>MONTH(F$2),DAYS(F$2,DATE(YEAR(F$2),MONTH(F$2),0)),DAY($C3)-1)))/$D3

    Dave,
    yes you are right first month is 27, but last month would be 20-1,19,so i added a -1 at C3
    =INDEX(FREQUENCY(ROW(INDIRECT($B3&":"&$C3-1)),EOMONTH(J$2,{-1,0})),2)*$E3/$D3

    I have attached the final sheet to show how its working, it appears to be working, not broken.

    Thanks again, this will contribute towards our snr management reporting, so you really helped.

    Cheers
    John
    Attached Files Attached Files
    Last edited by JOHNAK27; 08-10-2018 at 09:10 AM. Reason: wrong attachment
    Reply With QuoteRegister To Reply
  11. 08-10-2018, 03:29 PM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2405
    Posts
    13,430

    Re: Calc days in month when full and variable month for a budget report

    but last month would be 20-1,19,so i added a -1 at C3
    Thank you for clarifying that. Well done on the adjustment.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  2. [SOLVED] Convert two digit month (ex 10) to full Month Name in Variable
    By orozvik in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-30-2015, 05:42 PM
  3. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  4. year budget subtraction from month to month
    By kpross1227 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-24-2014, 05:45 PM
  5. [SOLVED] A formula to calc total number of days elapsed between two dates within any given month
    By paul.a.evans in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-03-2014, 01:29 AM
  6. [SOLVED] display cell to show only Month/year to do sumif calc if today()= any day of month
    By fireguy7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2013, 06:55 PM
  7. I'm looking for a formula to calc # of days left in a month
    By Manthony in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2006, 10:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1