Results 1 to 3 of 3

Thread: Calculate number of days used in current quarter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular NadoDude's Avatar
    Join Date
    Jun 2010
    Location
    Coronado, CA
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate number of days used in current quarter

    In my worksheet below I have a formula in Column E which is calculating the number of days used to date in the Current Month based on some criteria.

    In Column F, I need a formula which has the same criteria as in Column E, but want it to tell me how many days have been used to date in the current quarter.

    Worksheet: Sheet1 UsedRange: $A$1:$N$95 Range: $A$1:$G$6

     ABCDEFG
    1Planned Start DateActual Start DatePlanned End DateActual End DateThis Month to dateThis Quarter to DateNotes
    27/22/20107/22/20109/30/201012Should = 53
    36/15/20107/1/20108/10/20108/10/20100Should = 40
    45/1/20105/1/201010/15/20100Should = 92 (92 days in current Quarter)
    54/2/20104/2/20106/23/20106/23/20100Should = 0 (No days used in current quarter)
    69/18/201010/14/20100Should = 0 (Event has not started)

    AddressValueFormula
    E212=IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B2="",$A2,$B2),0)))
    E30=IF(B3="",0,MAX(0,MIN(IF($D3="",$C3,$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B3="",$A3,$B3),0)))
    E40=IF(B4="",0,MAX(0,MIN(IF($D4="",$C4,$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B4="",$A4,$B4),0)))
    E50=IF(B5="",0,MAX(0,MIN(IF($D5="",$C5,$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B5="",$A5,$B5),0)))
    E60=IF(B6="",0,MAX(0,MIN(IF($D6="",$C6,$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B6="",$A6,$B6),0)))
    I am Using Excel 2007 with Easy-XL Addin

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate number of days used in current quarter

    I'm not sure how row 4 would be 92 as we aren't yet at the end of the current quarter. If you use this version in row 2 copied down you get all the results you suggested...except row 4 is 74

    =IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($B2="",$A2,$B2),0)))

  3. #3
    Board Regular NadoDude's Avatar
    Join Date
    Jun 2010
    Location
    Coronado, CA
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate number of days used in current quarter

    You are correct - Row 4 should be 74. Thanks for keeping me honest. You solution appears to works great. Thanks for the assistance.
    I am Using Excel 2007 with Easy-XL Addin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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