Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unanswered: DateAdd Limitations

    I am generating a report that stores time(in minutes). This report needs to be able to be used for daily/weekly/monthly output. The daily and weekly work fine. When I attempt to generate the monthly aspects it chokes.

    =Format(DateAdd("n",[txtEmpTotMinutes],#12:00:00 AM#),"hh") & ":" & Format(DateAdd("n",[txtEmpTotMinutes],#12:00:00 AM#),"nn")

    Where [txtEmpTotMinutes] = the total minutes worked for month (10,560 to be exact)

    The date add returns 2:56:00 -- I believe that 2:56:00 is a correct return as I think 2 is how many time 60 has been returned(cycles?) 2*60+56 = 176 hours. Which is correct. Is it possible to return ##176:00## I have been trying for days any help is appreciated.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm confused.

    dateadd() adds some number (10,560 in your case) of intervals (n: minutes in your case) to an access datetime (midnight on 30th December 1899 in your case (or maybe midday*)) and returns an access datetime (in your case representing 08:00 on the 6th January, 1900 (maybe 20:00*)

    * sorry - i'm too lazy to lookup if 12:00:00 AM is 00:00:00 or 12:00:00 in 24-hour clock.

    so your result of 2:56:00 was so startling to me that i copy/pasted it in:
    Code:
    ? Format(DateAdd("n",10560,#12:00:00 AM#),"hh") & ":" & Format(DateAdd("n",10560,#12:00:00 AM#),"nn")
    08:00
    leaving me still confused. how on earth do you get 2:56:00 ?

    izy

    EDIT: changed 12:00 to 20:00
    Last edited by izyrider; 07-09-08 at 16:45.
    currently using SS 2008R2

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See if this works:

    Int([txtEmpTotMinutes]/60) & ":" & [txtEmpTotMinutes] Mod 60
    Paul

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Upon reflection

    Int([txtEmpTotMinutes]/60) & ":" & Format([txtEmpTotMinutes] Mod 60,"00")
    Paul

  5. #5
    Join Date
    Jul 2008
    Posts
    2

    Thank You

    YOU ARE THE MAN!!! THAT IS DEAD ON ... THIS MAKES MY DAY

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Glad it helped. Welcome to the site by the way.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •