Random Date calculation formats

http://

9/21/2007 2:23:01 PM #

Just what I needed  - thank you for sharing!!
  
          

http://

10/2/2007 4:55:56 AM #

Please suggest Date Calculation for finding duration between two Pick Date field by without coding (use rule or function built-in)

Thank you.

  
          

mlotter

10/2/2007 4:03:08 PM #

Hi,

You should be able to find the duration by just doing  =[End Date] - [Start Date].  This should give you the number of days that has elpased.

  
          

http://

10/2/2007 8:19:27 PM #

Thank you, mlotter. But when I used

[ToDate] - [FromDate] + 1

(pickdate control: ToDate and pickdate control: FromDate)

in insert formula then there was formula error.

The operand is not an expression.
-->[<--../my:ToDate] - [../my:FromDate] + 1

I'm not sure that I should convert my pickdate field value to datetime before.

Thank you very much.


  
          

mlotter

10/2/2007 9:49:46 PM #

Hi,

I guess I might be a little confused in what application you're tring this in.  The example that I gave you was for SharePoint calculated column type.  Are you trying this in SharePoint or InfoPath?

Cheers,
Michael

  
          

http://

12/3/2007 3:24:07 PM #

I am trying to work out which Yearly Quarter an items timespan is part of. Trouble is because my code is nested the timespans that starts early are "caught" and therefor are not found if f.eks "4. quarter" is chosen.

=
IF(OR(ISBLANK([Start date]),ISBLANK([End date])),"Start date or End date missing",
IF(OR(DATEVALUE("1/1/"&[Year])>[End date],DATEVALUE("3/31/"&[Year])<[Start date]),
IF(OR(DATEVALUE("4/1/"&[Year])>[End date],DATEVALUE("6/30/"&[Year])<[Start date]),
IF(OR(DATEVALUE("7/1/"&[Year])>[End date],DATEVALUE("9/30/"&[Year])<[Start date]),
IF(OR(DATEVALUE("10/1/"&[Year])>[End date],DATEVALUE("12/31/"&[Year])<[Start date]),
"","4. quarter "&[Year]),"3. quarter "&[Year]),"2. quarter "&[Year]),"1. quarter "&[Year]))

  
          

http://

12/12/2007 10:54:48 AM #

Hi,

I'm trying to get a formula to calculate hours for entries in a Time Log which span over several calendar days and where a running total of the hours between the two date/times is inappropriate (eg if someone sets start date as Monday and end date as Friday then the hours would be fixed as 37.5 based on a 7.5 hour working day).

This is primarily for internal codes for things like illness or vacation.

So far I can get it so that when this happens you don't get 120 hours allotted for a Monday-Friday appointment, but it returns #NUM! not 37.5 ... can you help?

This is what we've got so far:

=IF(
  NOT(ISBLANK([End Time])),
  IF(
    AND(Customer="Internal",(DATEDIF([End Time],[Start Time],"d")>0)),
      7.5 * (DATEDIF([End Time],[Start Time],"d")+1),
    ([End Time]-[Start Time])*24)
  ,0)

  
          

mlotter

12/16/2007 7:53:18 AM #

Hi Guy and Tim,

Sorry for not responding until know but I've been out of the country on vacation and had very limited access and very limited time to reply.  I was able to reply to simple things while gone but I didn't get too your guys questions.  I'll try to respond in the next couple of days.  If you guys don't mind please drop me an email with your question that way I can add them to my task list.

Cheers,
Michael

  
          

http://

1/21/2008 4:27:53 PM #

Thanks for the info. My problem is a little different. I am using sharepoint data view web part in sharepoint designer. I use filtering in the common data view tasks to filter documents that are less than 30 days old. How do I go about it? I converted the date to number and subtracted 30 from it. It does not do proper subtraction of days.
  
          

mlotter

1/23/2008 9:54:15 AM #

Hi Mandy,

Sorry for not responding sooner but my schedule has been crazy for the last few days.  I've spent a little time looking into the problem that you are having and I was not able to get the filtering to work either.  When I get some extra time I'll look into a little more and see if I can find a solution.

Cheers,
Michael


  
          

http://

2/13/2008 7:34:19 AM #

i`m using =text(month(date);"mmmm") to convert a date into a month`s name. But the result are wrongs, it`s returm me january instead of march and other similar error without logic.
I`m working in an spanish site, SPS 2003, and the date format is dd/mm/yyyy.
any ideas..... ?

  
          

Joshua

8/8/2008 5:46:52 AM #

Very informative.  Thank you again for your help Michael.  Your formulas helped me to set up the B & R Help Desk Ticketing site.