Anniversary Date

How do you calucate employee anniversary dates in excel?
0
tonnias (1)
1/9/2004 7:36:22 PM
excel.misc 78881 articles. 5 followers. Follow

29 Replies
620 Views

Similar Articles

[PageSpeed] 0

Tonnia
This will give you thier 5th anniversary date

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)

Assumes date start is in A1.  Adjust for other anniversaries

Good Luck
Mark Graesse
mark_graesser@yahoo.co
    
     ----- Tonnia wrote: ----
    
     How do you calucate employee anniversary dates in excel?
0
anonymous (74722)
1/9/2004 7:51:08 PM
Hi Tonnia!

With the joining date in A1:

Try:
=(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))
Returns the anniversary date this year.

If this year was not a Leap Year, then if the employee joined on
29-Feb the anniversary date would be returned as 1-Mar.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/9/2004 10:38:52 PM
I would like to create a formula to show me the next review date fo
employees. We do reviews every six months, so I would need a formula t
figure out how to show me when their next 6 month anniversary woul
be.

Any ideas?

Thanks in advance for any replies.

darro

--
Message posted from http://www.ExcelForum.com

0
1/12/2004 8:06:09 PM
Hi djeans!

This is easy to ask but as a formula it's quite difficult.

I have start date in A1 and today's date in B1

Try:
=IF(DATE(YEAR(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MON
TH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))),MONTH(IF(B1<DATE(
YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR
(B1)+1,MONTH(A1),DAY(A1))))-6,DAY(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))
)))>B1,MIN(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(
A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))),DATE(YEAR(IF(B1<DATE(
YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR
(B1)+1,MONTH(A1),DAY(A1)))),MONTH(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))
))-6,DAY(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1
),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))))),IF(B1<DATE(YEAR(B1),
MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MO
NTH(A1),DAY(A1))))

And even with this, you have a problem if the DoM of joining is >=29.

Life is a lot easier if you use "helper" columns! I used 3 to build
the formula:

E1
=IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))
Returns the next anniversary date
F1
=DATE(YEAR(E1),MONTH(E1)-6,DAY(E1))
Returns 6 months before the anniversary date.
G1
=IF(DATE(YEAR(E1),MONTH(E1)-6,DAY(E1))>B1,MIN(E1,F1),E1)
Returns the next 6 monthly anniversary.

With these helper columns I can get over the DoM >=29 potential
problem by:

F1
=DATE(YEAR(E1),MONTH(E1)-6,MIN(DAY($E$1),DAY(DATE(YEAR(E1),MONTH(E1)-5
,0))))

G1
=IF(DATE(YEAR(E1),MONTH(E1)-6,MIN(DAY($E$1),DAY(DATE(YEAR(E1),MONTH(E1
)-5,0))))>B1,MIN(E1,F1),E1)

I'm sure that these formulas can be improved upon but they seem to
test OK so....

I think that a VBA approach might be a lot easier.
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"djeans >" <<djeans.zxq87@excelforum-nospam.com> wrote in message
news:djeans.zxq87@excelforum-nospam.com...
> I would like to create a formula to show me the next review date for
> employees. We do reviews every six months, so I would need a formula
to
> figure out how to show me when their next 6 month anniversary would
> be.
>
> Any ideas?
>
> Thanks in advance for any replies.
>
> darron
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
1/13/2004 1:10:50 AM
Thanks Norman!

That has to be the longest Excel formula that I have ever seen.

You are truly an Excel Genius.

I tried entering dates higher than 29, and didn't seem to have any
problems that I could tell. Plus, it would be a shame not to use that
formula after you went to all that trouble.

Thanks again!

Darron


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 12:05:09 AM
Hi Darren!

The long version still suffered the defect of DoM >=29 and to correct
for that puts it beyond the formula length limit.

I'd recommend the helper column approach; apart from being inclusive
of DoM coverage, it's a lot easier to understand.

Alternative to formula would be a UDF but you'd have to use the same
logic as with building the formula.

On building; I cheated! I already had an anniversary formula and that
was adjustable for 6 monthly anniversary and then it was a question of
which to use.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/14/2004 12:22:52 AM
Can you explain the >=29 error?

I tested the formula with several dates and didn't seem to run into any
problems.

darron


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 2:59:16 AM
Hopefully this is an easy one.

I was trying to write a  formula to let me know if the next review was
an annual salary review, or a 6 month performance review.

In this example, Cell C2 is the hire date, and cell E2 is the next
review date. (the result of the long formula)

This is the formula that I tried.

=IF(MONTH(DAY(E2))=(MONTH(DAY(C2))),"Salary Review","6 Month Review")

The idea, was if the next review date had the same month/day as the
hire date, it would return the result, Salary Review, if not 6 month
review, but for some reason, it returns Salary Review for all of my
records.

Any help would be greatly appreciated.

thanks


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 3:27:56 AM
I may or may not know what I'm talking about, but, without loading int
excel and checking, aren't you donig this?

"=IF(MONTH(DAY(E2))=(MONTH(DAY(C2))),"Salary Review","6 Mont
Review")"

=(IF(The Month(Of the Day(In E2))=(The Month(Of The Day(In C2)))
"Salary", "6 Month")

Wouldn't you need to do 

=IF(MONTH(E2)=(MONTH(C2)),"Salary Review","6 Month Review")

I'm gonna go check that in Excel.

-Bo

--
Message posted from http://www.ExcelForum.com

0
1/14/2004 3:55:17 AM
Yeah, this makes it work.

=IF(MONTH(E2)=(MONTH(C2)),"Salary Review","6 Month Review")

-Bob


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 3:57:28 AM
But that doesn't take care of the day which was also part of the requirement
me thinks.


"beeawwb >" <<beeawwb.1006pq@excelforum-nospam.com> wrote in message
news:beeawwb.1006pq@excelforum-nospam.com...
> Yeah, this makes it work.
>
> =IF(MONTH(E2)=(MONTH(C2)),"Salary Review","6 Month Review")
>
> -Bob
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/14/2004 4:18:13 AM
I know, it's just all I could think of with my limited knowledge. 

Learning as I go here. Can you embed code like that for Day and Month
functions? As I understand it, =(Day) will give a number 1 through 7,
and =Month will give a number 1 through 12, assuming that a date is
inputted. I have no idea what happens if you put a 1-7 in =Month. Ie,
=Month(6). 

*checks*

It returns a "1" each time. So, maybe the way to do it, is to use
Nested If's... Or an IF/AND? Do those exist?

Like...

=IF(MONTH(E2)=(MONTH(C2)),(IF(DAY(E2)=(DAY(C2)),"Salary Review","6
Month Review")),"6 Month Review")

Is my syntax for that correct? I think it's donig what's asked when I
put it in Excel, but I'm not quite sure.

-Bob


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 5:47:39 AM
I tried this as well.

F2 is

=AND(MONTH(E2)=MONTH(C2),DAY(E2)=DAY(C2))

And G2 is

=IF(H2=TRUE,"Salary Review","6 Month Review")

Is that a better way to do it?

-Bo

--
Message posted from http://www.ExcelForum.com

0
1/14/2004 5:49:55 AM
That worked like a charm.

I am not very good at writing the formulas, obviously.

I really appreciate the help.

Darron


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 6:11:20 AM
May need an expert on this.  My comment only reflected the fact that the
question had not been answered and I was interested in a correct result
myself.
Rob

"beeawwb >" <<beeawwb.100bx5@excelforum-nospam.com> wrote in message
news:beeawwb.100bx5@excelforum-nospam.com...
> I tried this as well.
>
> F2 is
>
> =AND(MONTH(E2)=MONTH(C2),DAY(E2)=DAY(C2))
>
> And G2 is
>
> =IF(H2=TRUE,"Salary Review","6 Month Review")
>
> Is that a better way to do it?
>
> -Bob
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/14/2004 6:20:19 AM
and combining those....
=IF(AND(MONTH(E4)=MONTH(C4),DAY(E4)=DAY(C4))=TRUE,"Salary Review","6 Monthly
Review")
Rob

"djeans >" <<djeans.100cwu@excelforum-nospam.com> wrote in message
news:djeans.100cwu@excelforum-nospam.com...
> That worked like a charm.
>
> I am not very good at writing the formulas, obviously.
>
> I really appreciate the help.
>
> Darron
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/14/2004 6:28:43 AM
Hi djeans!

Try it on employment commenced 31 August.

Incidentally, the formula might be made a tad shorter using EDATE but
that makes use of the file dependent upon Analysis ToolPak being
installed and selected as an Addin.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"djeans >" <<djeans.10040q@excelforum-nospam.com> wrote in message
news:djeans.10040q@excelforum-nospam.com...
> Can you explain the >=29 error?
>
> I tested the formula with several dates and didn't seem to run into
any
> problems.
>
> darron
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
1/14/2004 6:40:58 AM
Hi djeans!

Not too difficult. I get:

=IF(MONTH(E2)=MONTH(C2),"Salary Review","6 Monthly Review")

Seems to test OK.

What does it say?

If the month of the 6 monthly review is the same as the month of
joining it must be the (Annual) salary review. If it isn't then it
must be the 6 monthly performance review.

There's no need to test for the day because the day is not the
decider. In other circumstances you'd need to use a more complicated
formula for determining the anniversary date but here the formulas
that build E2 have done this.

BTW the joining on 31-Aug-2000 confirms the need for the helper
formulas given before. It won't just be an inconvenience and peculiar
date but will also screw up this formula.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/14/2004 7:07:14 AM
I entered a hire date of 8/31/00 and the formula returned a next revie
date of 3/2 - 6 month review. Is there an error I am not seeing?

I am using Office for Mac if that makes a  difference.

I hate to abuse a great resource, but If I could ask one more question
It would be a great help.

I also would like to have a column (or two if necessary) that will tel
me how many years and months an employee has worked  for us.

For example, if the employee was hired 2/19/92 and today is 1/14/04 
would like it to tell me that they have worked for me 11 years and 1
months. 
Is that possible?

You are all awesome.

Darro

--
Message posted from http://www.ExcelForum.com

0
1/14/2004 4:41:31 PM
Hi djeans!

Re the >= 29 problem..

For 31-Aug-2000 start date the next 6 monthly date will be returned as
2-Mar-2004. But after 2-Mar-2004 it will be returned as 31-Aug-2004
and after 31-Aug-2004 it will be returned as 3-Mar-2005. It's not so
much an error as a definitional difference.

Most people, (EDATE and Edit > Fill > Series) would prefer a return of
the last date of the month 6 months hence if the DoM of base month
doesn't exist. The problem gets worse if you try to copy down a series
of equally spaced time periods.

Typical way of adding months is to use:
Where AddMons is the number of months to be added:
=DATE(YEAR(A1),MONTH(A1)+AddMons,DAY(A1)
Try that with 31-Aug-2003 with different AddMons and copy each down
awhile and you'll see the problem.

One solution is:
=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH
(A1)+1+AddMons,0))))

In an earlier reply I found that I ran out of allowable formula length
using that approach. Peter Dorig's approach is more efficient:
=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))

However, I don't think that these super long formulas are a good idea
if the use of helper columns is possible. Hence my alternative
solution earlier in this thread.

For calculation of years and months of employment use:

=DATEDIF(Start_Date,End_Date,"y")&" years
"&DATEDIF(Start_Date,End_Date,"ym")&" months"

Best way of entering dates is by cell references to cells that contain
dates.

See Chip Pearson's site for the details of the mysterious Excel
Built-In function DATEDIF (and a whole lot more):

http://www.cpearson.com/excel/datedif.htm


-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/14/2004 8:44:56 PM
I have gone with the helper column idea and hid the helper colums s
that they don't confuse myself or anyone else who may use the page.

You have all been a great help.

I thank you ALL.

Darro

--
Message posted from http://www.ExcelForum.com

0
1/15/2004 11:03:38 PM
Hi Darron!

Pleased to help and hear that it's all working OK.

Keep posting! That next 6 monthly anniversary question has quite wide
applications and I've not tried to solve it before. As an example, we
might use it for determining the next rental payment date based upon a
lease commencement date.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/16/2004 4:11:49 AM
I have the spreadsheet set up using the helper columns, but there seems
to be something funny going on.

All of the 6 month review dates are listed as being on the same day of
the month, regardless of the hire day.

For instance, Hire date of 8/18/03 shows the next review being as 2/2,
and hiredate of 8/26/03 shows next review as 2/2 also.

Is this correct based on the helper columns?

It's not a major issue, but just seems odd.

Darron


---
Message posted from http://www.ExcelForum.com/

0
1/18/2004 4:00:00 PM
Maybe I have something misconfigured.

A2 = last name
B2 = First name
C2 = Hiredate
D2 = Today()
E2 =IF(D2<
DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE(YEAR(D2)+1,MONTH(C2),DAY(C2)))

F2
=DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2)-5,0))))

G2
=IF(DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2)-5,0))))>D2,MIN(E2,F2),E2)

With E2 and F2 being the helper columns, and G2 being the next review
date.

Do I have it right?

darron


---
Message posted from http://www.ExcelForum.com/

0
1/18/2004 8:16:00 PM
Hi Darron!

I've checked your formulas against mine and they are exactly the same.
So you have a difference on your workbook somewhere.

The day of the date for the review is taken from the date of hire
except where that date >=29 in which case it is the last day of the
month if the hire day doesn't exist.


-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"djeans >" <<djeans.108uom@excelforum-nospam.com> wrote in message
news:djeans.108uom@excelforum-nospam.com...
> Maybe I have something misconfigured.
>
> A2 = last name
> B2 = First name
> C2 = Hiredate
> D2 = Today()
> E2 =IF(D2<
>
DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE(YEAR(D2),MONTH(C2),DAY(C2)),DATE
(YEAR(D2)+1,MONTH(C2),DAY(C2)))
>
> F2
>
=DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2)-5
,0))))
>
> G2
>
=IF(DATE(YEAR(E2),MONTH(E2)-6,MIN(DAY($E$2),DAY(DATE(YEAR(E2),MONTH(E2
)-5,0))))>D2,MIN(E2,F2),E2)
>
> With E2 and F2 being the helper columns, and G2 being the next
review
> date.
>
> Do I have it right?
>
> darron
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
1/19/2004 12:00:46 AM
enter in several hire dates in the august - december range

Let me know if you get review dates that are all the same day in each
month, or if they are different.

darron


---
Message posted from http://www.ExcelForum.com/

0
1/19/2004 12:21:43 AM
Hi Darron!

All dates have same DoM as the hire date. Would you like a copy of my
test workbook?

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/19/2004 12:51:07 AM
That might help me figure out what the problem is.

thanks

darro

--
Message posted from http://www.ExcelForum.com

0
1/19/2004 1:56:19 AM
Hi Darron!

Send email to address below and I'll reply with attachment.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/19/2004 2:40:25 AM
Reply:

Similar Artilces:

DPM 2010 release dates
Hi, I was wondering if anyone could define what the current projected release dates for DPM 2010 are? I'm specifically looking for RC and RTM dates. I've heard that the RTM is now expected to ship in early summer and wanted to confirm. Here is an old information I gathered before. I am not sure it is still accurate. So lets wait for the DPM team's answer: http://santhoshsivarajan.blogspot.com/2009/12/dpm-2010-timeline.html -- Santhosh Sivarajan | MCTS, MCSE (W2K3/W2K/NT4), MCSA (W2K3/W2K/MSG), CCNA Houston, TX http://blogs.sivarajan.com/ http://publications...

creating rule based on date email received
I would like to create a rule to move message to a specific folder based on their received date - i.e. anything older than 7 days, move to this folder. Can rules be made based on a general date, or do you need to put in specific dates for it to work. I do not want to archive the email, just move it to a separate folder for later review. Any help is appreciated. Thanks! "Diana" said in news:6b0b01c3e69d$9d45ff10$a001280a@phx.gbl: > I would like to create a rule to move message to a > specific folder based on their received date - i.e. > anything older than 7 days, ...

SharePoint 2010 Beta expiry date
Is there any expiry date set on SP2010 Beta? If yes, where can I view that date? TIA J Justin here is the link you get the details on that http://blogs.msdn.com/ekraus/archive/2009/11/17/sharepoint-2010-beta-2-is-finally-here.aspx Justin Jeyaraj wrote: SharePoint 2010 Beta expiry date 28-Apr-10 Is there any expiry date set on SP2010 Beta? If yes, where can I view that date? TIA J Justin Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Free Online Courses Available for Eggheadcafe.com Users http://www...

Grouping mails by their date received
Hi there, I would like to group my mails on "Received" date so as to figure out my activities on a particular date. Microsoft Outlook 2K SR1 allows its users to format the columns the way they want and group mails by those columns (except columns with formula) When I customize the "Received" column with "mm/dd/yy" format and group by it, Outlook falls back to the original format ignoring my customization. This results in a useless view in which Outlook groups the mails upto minutes accuracy and ends up puting one mail on each group and creating as many groups ...

RMS should have a search strategy for "date account opened"
EOM ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=0b38650c-32cb-40cb-8b52-e4aab95e11f9&dg=microsoft.public.pos you can do this with reports. "samax" <s...

Vba Date$
I have to use the Date$, and this seems to be swapping the day and month: The system date is 04/06/2010 (dd/mm/yy), and the regional setting are for the UK when I try Dstr = Format(Date$, "dd/mm/yy"), I get 06/04/2010, and when I break it down into individual elements, ie Y = Year(Date$) M = Month(Date$) D = Day(Date$) I get M = 4 & D = 6! Please help! -- Eric Try using DStr = Format(Date, "dd/mm/yy") -- HTH Bob "Eric" <Eric@discussions.microsoft.com> wrote in message news:A93C4128-4E48-46B3-A59B-3...

automatically saving a workbook as today's date
I have set up a template and run a macro that copies information fro one workbook to another, then saves the changes and then closes. Thi is fine, but before the workbook closes I am trying to get the macro t rename the template with today's date, but without success. I have pu =today() in cell a1 and try to get the vba to look at this cell befor closing but it just saves the template as (a1).xls instead of today' date. Does anyone know what I am doing wrong. Your help will be gratefully received regards Steve Jackso -- Message posted from http://www.ExcelForum.com One way: ...

How do I enter a date in excel without "/" ex. 041504 will show 0.
Excel can't do this without some help. You could look at: http://www.xldynamic.com/source/xld.QDEDownload.html for an addin that can set this up. tj "Amy" wrote: > ...

Date Filtering in RMS Active Reports
My company is open past midnight. This causes terrible headaches for me and reporting since RMS uses the standard 24 hour day, yet I need sales that occur after midnight but prior to 3 am to appear on reports for the previous day. I have a datetime field that I am converting using the formula Formula = "CONVERT(nvarchar, ViewTenders.Time, 22)" so that in the active report it looks like 11/08/06 1:54:00 PM. In the active report, it's vbDataType is defined as vbString. I can not get the filter(s) to allow me to show dates correctly. What I'd like to see is all transa...

Languages in date colomn
After downloading my email through a swedish network, the weekdays and dates of recieved emails are stated in swedish in my inbox. My Outlook 2000 is Norwegian language. How can just this colomn change language, and is there a way for me to change it back to Norwegian ? Thank you in advance. anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> wrote: > How can just this colomn change language, I don't know, but I've heard of it. > and is there a > way for me to change it back to Norwegian ? Here's something to try: at a command line pr...

Always future dates
I have a form with an unbound text box for entering a date. The text box's format property is set to Short Date. Here's the problem, based on today (09/14/07): If a user enters 01/15 into the text box, Access will expand that date to 01/15/07. The users would like the date to be expanded to 01/15/08, the future date instead of one in the past. Can someone suggest the easiest way to force the date into the future? Is there some sort of formatting property I can set, or would this need to be done in code? If it's in code, have you seen a sample anywhere that would give me a good sta...

=DATEDIF(start date,end date, unit)
When I use this function, the error #NAME? is returned. Could this because the function requires an ADD-IN or extra components installed? DATEDIF is described in my version of HELP but I notice it is not shown in the list of functions that appears when I select "Paste Function" from the toolbar and select the category "Date&Time". This is why I think perhaps the problem is that support is not installed. I'm using Excel 2000 V9.0.2720 Yes, tools>addins>analysis toolpak -- Don Guillett SalesAid Software donaldb@281.com "gvm" <postbox@tech...

Bold dates on yearly calendar
I am creating a yearly calendar using a publisher template. How can I bold certain dates on the calendar? When I click on a particular month, all the text for that month gets selected. Thank You About the only way I know is to select the month text box, copy, paste special as a table. The table will be large but can be resized to the same size as the text box by using the size handles. As for the month name cell... select the top row of the table, Table menu, merge cells. You should be able to bold the dates now. -- Mary Sauer http://msauer.mvps.org/ "Ana24" <Ana24@disc...

Count by short date and group by agent name
I have a tracking table (tbl_Tracker) that now contains date and time in one field [Date] and agent name in another field [SLR]. I would like run a query to do a count of number of records by agent by day. How do I set up the query to recognize only the date and not the time. I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a record for each date and specific time instead of grouping the whole day. Then I need to build another query that looks at another table (tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a date range and ...

Report Generation Variables for Date
I'm trying to define a filter in a report, using the last month start and last month end as low and high limits. Can somebody tell me the name of this variables, like <Today> is self explanable. -- alvaroguzman this is all I have: <MonthStart> first day of month <WeekStart> date of first day of this week <LastWeekStart> date of first day of last week <YearStart> first day of year <Now> current date/time <ReportDate> date of report "Alvaro Guzman" <aguzmanc2005@hotmail.com> wrote in message news:7C2DA569-6C0C-420C-9F49-4FC...

Conditional Formatting Dates 02-18-10
I am attempting to highlight past due dates (I am horrible with the formula's and can not seem to figure out how to NOT highlight if there is no information in the cell's) A1=Planned date and A2=Completed date Conditions I am trying to figure out 1. Would like A1 to highlight RED if less than Now and A1 is NOT BLANK or A2 is BLANK 2. Would like A2 to highlight green if filled out and less than or equal to A1 Thanks anyone that can help!!! Katie CF formula for A1: =OR(ISBLANK(A2),AND(A1<TODAY(),ISNUMBER(A1))) CF formula for A2: =AND(ISNUMBER(A2),A2<=A1...

Last Meeting Date
I am looking for a way to populate the last meeting date into a custom field of a contact entity. I did not see how to do this with the MS Workflow. You can't do that from workflow unless you going to write a workflow assemly to pull the information through web service or filtered views. Are you familiar with the CRM 3.0 SDK? Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Mar 30, 10:10 am, "tim" <computerti...@gmail.com> wrote: > I am looking for a way to populate the last meeting date into a custom > field of a contact entity. I did not see how...

Check inputdate, then retrieve data to fill in chart from dated co
ok hope I can explain this.. I have a sheet with dated columns and the first row is employee names each dated column is filled with letters that correspond to a duty a-g. so for the next 3 months I can look across the top for a date then down the column to see where each person is supposed to be. Now I also have a form I print up daily to post for people to see where they are supposed to be.. I fill it manually at this point I wonder if there is any formulat to help automate this.. I am thinking two hopeful ideas.. a drop down list on the form, pulling the list of names......

Downloaded Date Wrong
When I'm viewing a specific account, in the upper left pane is the DOWNLOADED information; date, balance and items to review. For some reason, tha date, balance etc. is no longer being updated when I download statments from my bank. Any ideas how to get that nice feature back up and running? I think that one comes from your bank in the downloaded file, so you need to ask them or choose OFX as the download format if that is an option for you,. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Mon...

Save as date on exit
im currently working on a "cashout" sheet in which different ppl enter their data and do the cashout accordingly. right now - the sheets are all being saved as hard copies and the information entered in the spread sheet is not saved and the next person carries on as described. what the owner of the company would like is an auto back-up of this proceedure, where as the data entered into the spread sheet is saved after the current session is closed. im not too sure how to correctly describe what i need - but i think that's it... instead of not saving the data - the data will...

Textbox Code for Date Format
Pretty sure it's something silly on my part. form contain combobox 'Date' (It looks up dates from specified dates within payroll timeframes) No-Control text box placed next to combo box to have Day of Week appear to keep people on right day. (I don't like the calendar idea, because I need to keep people in the correct payroll date range and other items.) For text box, from property window / data tab / control source I've entered =Formate([Date],"dddd") expecting the day of the week to appear on focus. Field populates fine but shows wrong day of th...

Date Validation pop up
Hi Everyone, I was just asked to create a date validation pop up on a form that I'm designing. The validation is a pop up box that asks the user if the end date of the period is correct by taking the last date of the previous period and adding the number of days in the current period. So one field on the form asks for the end date of the last report period and another field asks for the number of days in the current period. For example the last day of the previous review period could be April 30, 2007 and the number of days in the current period is 7 days. So they would like a pop up t...

Using =now() to display date format like "mmdd"
I am building a quoting tool and for the quote number, I am trying to concatenate "QUO", the first 3 characters of the customer name, and the month & date. So what I want is: QUO-SON-0225 But when I use the "NOW" command for the date, I get: QUO-SON-39869.3365444444 How can I reformat the result of the "NOW" command to get just the "mmdd" that I'm looking for? Hi Try ="QUO-"&LEFT(Customer,3)&"-"&TEXT(NOW(),mmyy") where Customer is the cell ref containing the Customer name -- Regards Roger Govier <r...

Question on sorting dates
After I download data from a database, the data in the date column is in date format, as for example, "11/02/2004". To make sure they are in date format, I format them again. Then I tried to sort the date in an ascending order, it doesn't work. It did some sorting work, but it put November dates right after January dates, and then Feb, Mar.... I guess Excel doesn't really take this column of data as dates. Any help is appreciated. Jason What happens if you test with a formula =ISTEXT(A2) if it returns TRUE the dates are text, if so, copy an empty cell, select the ...

Date function for "Last Quarter"
I'm trying to define a function for the last quarter that when selected will allow me to populate "Start from" and "To" text boxes. I can make the To bit work but not the From. The code I currently have is: Me!txtDateFrom = DateAdd("q", -1, DateSerial(Year(Now()), DatePart("q", Now()), 1)) As of the date of posting (20 March 2010), this correctly produces 01 October 2009, but if I go back in time by resetting my computer date, it produces a date of 01 January 2009 (instead of 01 July 2009). My formula is clearly wrong somewhere but d...