1. 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

2. 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. 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. 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. 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. 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. 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. 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

9. 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. 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. 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. 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
•