Solved

How to subtract dates in SharePoint 2007 columns?

Posted on 2013-05-15
8
741 Views
Last Modified: 2013-05-15
Dear EE experts,

We would like to ask on how to subtract dates in SharePoint 2007 columns? We would like to get the total number of Years/Months/Days that the staff worked in the company.
We have:
3 Columns
- Date Start
- current date
- Total years in work

If we subtract the 'Current date' column to 'date start' column, it will give us different date...

What we want to happen is something like:
= Current date - Date start
= May 15, 2013 - January 2, 2007
then it should give us 6yrs, 5months, & ...

thank you in advance & hope to hear soon...
0
Comment
Question by:Stiebel Eltron
  • 3
  • 3
  • 2
8 Comments
 
LVL 21

Expert Comment

by:Haresh Nikumbh
ID: 39167522
have you check this ?

well i am not share point expert but following link may work for above requirement.

http://office.microsoft.com/en-in/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx
0
 

Author Comment

by:Stiebel Eltron
ID: 39167528
@takecoffe, yes, but we didn't get any possible solution for our query... :(
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39167552
Hi,
here we go:
=DATEDIF([Date start], [Current date], "y") & " yrs, " & DATEDIF([Date start], [Current date], "ym") & " months and " & DATEDIF([Date start], [Current date], "md") & " days"

Open in new window

0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 500 total points
ID: 39168014
And thats how it looks like:
Sample data - resultsHTH
Rainer
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:Haresh Nikumbh
ID: 39168024
Cool
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39168246
One question for me: how do you get the current date?
[TODAY] does not work in calculated columns and the famous hack with a TODAY column is useless as the value will only be calculated when the record is modified.
Thanks.
Rainer
0
 

Author Comment

by:Stiebel Eltron
ID: 39170237
@RainerJ: We're about to ask that question, regarding 'TODAY' column. it's not updating automatically, it's so annoying but what we're doing is we go inside that 'TODAY' column we don't do anything, we just click 'OK' then it will update the today's date... but we're still searching for a solution that will update that 'TODAY' column automatically... hope there is... :)
0
 

Author Closing Comment

by:Stiebel Eltron
ID: 39170239
Thank you so much RainerJ! That is what we need... :)
but we need to search for the 'TODAY' column, that will update automatically...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now