Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I round a date?
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(B610/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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I round a date?
If I have understood:
=IF(DAY(C1)<15,DATE(YEAR(C1),MONTH(C1),1),DATE(YEA R(C1),MONTH(C1),14)) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cyndy" wrote in message ... 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(B610/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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I round a date?
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(B610/1/2005,EDATE(B6,3),EDATE(B6,6))-MOD((IF(B610/1/2005,EDATE(B6,3),EDATE(B6,6))-$C$1),$D$1)+$D$1 -- HTH, Barb Reinhardt "Cyndy" wrote: 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(B610/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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I round a date?
Just a warning...
10/1/2005 is 10 divided by 1 divided by 2005. I bet you wanted: =if(b6date(2006,10,1),edate(... (and the same change later in the formula) Barb Reinhardt wrote: 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(B610/1/2005,EDATE(B6,3),EDATE(B6,6))-MOD((IF(B610/1/2005,EDATE(B6,3),EDATE(B6,6))-$C$1),$D$1)+$D$1 -- HTH, Barb Reinhardt "Cyndy" wrote: 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(B610/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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round Date & Time | Excel Discussion (Misc queries) | |||
Date Round Up | Excel Discussion (Misc queries) | |||
calcutale 2 date with round date | Excel Worksheet Functions | |||
Date round-up | Excel Discussion (Misc queries) | |||
Round date to first of month | Excel Discussion (Misc queries) |