How do I round a date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cyndy - 20 Sep 2007 14:44 GMT

I'm trying to round a date to the first day of the next (14 day) pay cycle to
show when vacation time is accrued. Right now, I have:

- In cell B6 I manually enter the Date of Hire
- To show when benefits start, 6 months after the date of hire (or 3 months
if hired before 10/1/2005), in cell D6
=IF(B6>10/1/2005,EDATE(B6,3),EDATE(B6,6))
- To show when vacation is first accrued, 12 months after the benefit date,
in cell A10 =EDATE(D6,12)

I would really appreciate any help!
Thanks!
Cyndy

Reply to this Message

Bernard Liengme - 20 Sep 2007 15:08 GMT

If I have understood:
=IF(DAY(C1)<15,DATE(YEAR(C1),MONTH(C1),1),DATE(YEAR(C1),MONTH(C1),14))
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> I'm trying to round a date to the first day of the next (14 day) pay cycle
> to
[quoted text clipped - 12 lines]
> Thanks!
> Cyndy

Reply to this Message

Barb Reinhardt - 20 Sep 2007 15:10 GMT

It would help to know when your paycycles begin. Let's assume that a
paycycle begins on 9/1/2007 (C1) and begins every 14 (D1) days.

=IF(B6>10/1/2005,EDATE(B6,3),EDATE(B6,6))-MOD((IF(B6>10/1/2005,EDATE(B6,3),EDATE(B6,6))-$C$1),$D$1)+$D$1

Signature

HTH,
Barb Reinhardt

> I'm trying to round a date to the first day of the next (14 day) pay cycle to
> show when vacation time is accrued. Right now, I have:
[quoted text clipped - 9 lines]
> Thanks!
> Cyndy

Reply to this Message

Dave Peterson - 20 Sep 2007 17:55 GMT

Just a warning...

10/1/2005 is 10 divided by 1 divided by 2005.

I bet you wanted:

=if(b6>date(2006,10,1),edate(...

(and the same change later in the formula)

> It would help to know when your paycycles begin. Let's assume that a
> paycycle begins on 9/1/2007 (C1) and begins every 14 (D1) days.
[quoted text clipped - 18 lines]
> > Thanks!
> > Cyndy

Signature

Dave Peterson

Reply to this Message