Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Field with Formula (2003)

    Hi there

    I'd like to create a custom field in the calendar views. I'd like the custom field to display a date for each appointment. Basically if the start time of the appointment is before 6am I'd like the date to show as the previous day, otherwise I'd like the correct date to display. e.g. An appointment starting Wed 26 Mar 05:00hrs would display as Tues 25 Mar.

    When I try to create a formulae I can only find a way to work with the start field rather than the start time. Is there anyway to do this?

    Any help would be much appreciated.

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Field with Formula (2003)

    Try this:

    DateSerial(Year([Start]),Month([Start]),Day([Start]))+(Hour([Start])<6)

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Field with Formula (2003)

    Thanks Hans

    That works a treat, I've changed it slightly to
    Format(DateSerial(Year([Start]),Month([Start]),Day([Start]))+(Hour([Start])<6),"ddd dd/mm/yy")
    I'm not sure how it works though. I also came up with
    IIf(Hour([Start])<6,DateAdd("d",-1,[start]),[start]) which also seems to work.

    Thanks

    Amanda

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Field with Formula (2003)

    Start contains both the date and time. DateSerial(Year([Start]),Month([Start]),Day([Start])) returns the date-only part of the Start field.
    Hour([Start])<6 is True if the time part of Start is before 6 AM, False otherwise. In VBA, True = -1 and False = 0, so if the time is before 6 AM, -1 is added to the date, in other words, 1 (day) is subtracted.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Field with Formula (2003)

    Wow. You're good

    Thanks for the explanation

Posting Permissions

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