none
Calculated Column: Text ==> Date and Time Column (Date+Time)

    Question

  •  

    Hello,

     

    I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)

     

    How can I have a calculated column which is a Date+Time based on a text_column

    Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time

     

    Example:

    20070629_0848 converting to 29/06/2007 08:48

     

    ==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)

     

    Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...

     

    It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....

     

    thx

    Frederik

     

    Friday, June 29, 2007 6:53 AM

Answers

  • Hey Frederik,

     

    As WSS 3.0 uses an Excel-like formula for calculated fields, have you tried doing a =DATE(....) + TIME(....) calculation?  It works in Excel, and might very well work in WSS 3.0.  By the way, WSS 3.0 / MOSS 2007 should use calculated fields in the same fashion.

    Tuesday, July 03, 2007 3:21 AM

All replies

  • Hey Frederik,

     

    As WSS 3.0 uses an Excel-like formula for calculated fields, have you tried doing a =DATE(....) + TIME(....) calculation?  It works in Excel, and might very well work in WSS 3.0.  By the way, WSS 3.0 / MOSS 2007 should use calculated fields in the same fashion.

    Tuesday, July 03, 2007 3:21 AM
  •  

    Incredible,

     

    It is the solution and worked very well.

    In the meantime, I did changed a lot of my logic; the date-field has to be calculated from an text-infopath-column. And because I couldn't find the solution, I changed my infopath + publishing.

     

    But I have learned how It works now and will use it in further projects.

     

    Thx very much

     

    Frederik

     

    Tuesday, July 03, 2007 7:17 AM
  • Hi,
    I used some excel formula to calculated column but it didn't work.
    What I want to do is working out networkdays which calculates weekdays not weekends.
    In excel, it is done by networkdays(C1,C2). so it just calculate the number of days between two days excluding weekends.
    Do you know how  to apply this at Moss 2007?

    Thanks,
    Friday, July 06, 2007 1:04 AM
  • Instead of NETWORKDAYS, you may have to come up with a custom formula using the WEEKDAY function.  This is not a solution, but at least a start to an Excel formula that may or may not work in SharePoint calculations:

     

    Code Snippet
    =DAY([EndDate]-[StartDate]-WEEKDAY([EndDate])+WEEKDAY([StartDate]))/7*5+MIN(MAX(WEEKDAY([EndDate]),1)-MAX(WEEKDAY([StartDate]),1), 4)+1

     

     Hope this works for you.

     If not, please start a new thread.

    Friday, July 06, 2007 3:55 AM
  • Hi!

     

    I'm trying to use the same solution for a different problem, i have created a calculated column of type "Date and time" and have the formula: =DATE(2009,1,1) + TIME(12,0,0) and get the response "The formula contains a syntax error or is not supported"

     

    What am I doing wrong?

    Thursday, June 12, 2008 9:57 AM
  • Hi Guys,,

     

    This is known issue in microsoft. this issue will be fixed in next MOSS SP2 release.

     

    Cheers

     

    NK 

     

    Wednesday, July 09, 2008 5:47 AM
  • Nirju,

    Do you have any references to this issue? Maybe a KB link?

    Thanks,

    Diogo
    Tuesday, July 29, 2008 4:51 PM
  • Jonha134 said:

    Hi!

     

    I'm trying to use the same solution for a different problem, i have created a calculated column of type "Date and time" and have the formula: =DATE(2009,1,1) + TIME(12,0,0) and get the response "The formula contains a syntax error or is not supported"

     

    What am I doing wrong?


    In my case, replacing commas with semicolons solved the problem:

    =DATE(2009;1;1) 

    Friday, August 01, 2008 12:21 PM
  • Kudos Diogo!

    Had a major problem with =TEXT(WEEKDAYS(Column1),"ddd")

    After quite a search I stumbled upon your reply about the semicolons...

    so I ended up with
    =TEXT(WEEKDAY([Due Date]);"ddd")

    which works like a charm!

    Gracias!

    Tuesday, March 02, 2010 12:49 PM