Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date round (Excel 2003)

    Hi Excel Experts,

    I am working on a formula to get the duration an employee has spent in 2 roles in last 1 Year i.e. 01-Oct-06 to 30-Sept-07. I have created the formula, however i need to round it off

    i.e. if the Date is < 15, consider it as one month, if > 15 then 0.

    I have column A - Date of Joining(DOJ)
    Column B - Date of Role Change which will either have a date or "No Change"
    Column C - (formula for Role in Previous Role)
    Col D - Formula for Role in Current Role)

    The reason i need this formula is if someone has joined between 1st & 15th of Oct it shows 11 Months total. I should be 12, and if > 15th oct I should show 11. Similarily for Role Change.


    Can you please review and let me know how i can have the rounding-off done, also is it possible to make the formula smaller. Attached is the file for your reference.

    Regards
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date round (Excel 2003)

    Are the dates 01-Oct-06 and 30-Sep-07 fixed or should they be variable? For example, do you want 01-Nov-06 and 31-Oct-07 next month? Or do you want 01-Oct-07 and 30-Sep-08 next year?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Hi Hans,

    The dates are fixed i.e. 01-Oct-06 to 30th Sept-07.
    If DOJ is < 01-Oct-06 and Col B=No Change then duration in previous role =0
    else, duration is previous = 01 Oct-06 till Col B
    Duration in Current Role = Date of role change to 30-Sept-07

    Hope this clarifies, i also need the formula to check if the DOJ or Date of Role change is < 15th of the Month then the entire month else next month.

    Regards
    Baiju

    Regards
    Baiju

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Hi Hans,

    Any luck on this query?. Is my question clear or do u need more clarifications

    Regards
    Baiju

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date round (Excel 2003)

    The replies in the thread starting at <post:=667,933>post 667,933</post:> give you the basic idea. You can adapt this for other situations.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date round (Excel 2003)

    Could you put an example with representative months and explain show the values desired? I am not sure what numbers you get that are wrong or what you actually desire.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Hi Steve,

    All our employees are going through their appriasals for the Period 01-Oct-06 to 30th Sept-07 i.e. 12 months.
    I have employees who have joined before 01-0ct-06 and after 01-Oct-06. Also there are employees who have changed roles during this period.

    In my attachment in
    Col A is the Date of Joining.
    Col B Role Change Date.
    Col C time spent in Previous role (if any role change).
    Col D time spent in Current Role. Col
    Col E Total Duration i.e. from 01-0ct-06 to 30-Sept-07 (Should not be more than 12)

    I am looking at a formula in Col C & D. The logic
    FOR Column C
    If Col B = No Change AND if Col A < 01st Oct-06 THEN 0
    If Col B = No Change AND Col A > 01st Oct-06 THEN 0
    if Col B <> No Change AND Col A < 01-Oct-06 THEN Total Months from 01st Oct-06 to Col B
    if Col B <> No Change AND Col A > 01-Oct-06 THEN Total Months from Col A to Col B

    FOR Column D
    If Col B = No Change AND if Col A < 01st Oct-06 THEN 12
    If Col B = No Change AND Col A > 01st Oct-06 THEN Months Between Col A to 30-Sept-07
    if Col B <> No Change THEN Total Months from Col B to 30th Sept-07

    Hope this clarifies, i have used a formula in the attachment, just let me know if its fine or it could be made smaller. Or is there another simpler formula.

    Regards
    Baiju

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Hi Hans/Steve,

    I Managed to build the formula.. however i still find a diff of 1. Can you please review the attachment and let me know where this diff is coming from.

    Formulas in Col C & D are the one's that i want you to review. The total of Col C & D is Col E and should be the same as Col F. However in some cases there is a diff on 1 between Col E & F.

    Regards
    Baiiju
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date round (Excel 2003)

    The ones that do no work are the ones with role change >14 since you eliminate a month. Your "total" is the total so is correct. if the date is ">15" the previous and the current ignore this month. If you increase the date for the "previous" calc to go to the end of the month, you must decrease the date so that the month is picked up Current:

    C2:
    =IF(AND($A2>DATE(2006,10,1),$B2=0),DATEDIF($A2-DAY($A2)+1-14*(DAY($A2)>=15),DATE(2007,10,1),"M"),IF(AND($A2< DATE(2006,10,1),$B2=0),12,IF($B2<>0,DATEDIF($B2-DAY($B2)+1-14*(DAY($B2)>=15),DATE(2007,10,1),"M"))))

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Hi Steve,

    Thanks for the formula, however im getting a negative 1 difference now for all those have joined > 15th... am i going wrong somewhere i.e. col A

    Regards
    Baiju

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date round (Excel 2003)

    Sorry, I seemed to have corrected too much. Try this in D2:

    =IF(AND($A2>DATE(2006,10,1),$B2=0),DATEDIF($A2-DAY($A2)+1+14*(DAY($A2)>=15),DATE(2007,10,1),"M"), IF(AND($A2<DATE(2006,10,1),$B2=0),12,IF($B2<>0,DAT EDIF($B2-DAY($B2)+1-14*(DAY($B2)>=15),DATE(2007,10,1),"M"))))

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date round (Excel 2003)

    Thanks Steve,

    The formula is working for me

    Regards
    Baiju

Posting Permissions

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