Consulting

Results 1 to 5 of 5

Thread: MS Project Custom Fields Formula calculate start in timezone with daylight saving

  1. #1
    VBAX Newbie
    Joined
    Feb 2022
    Posts
    5
    Location

    MS Project Custom Fields Formula calculate start in timezone with daylight saving

    In Custom Fields type Date we now use the Formula:
    ProjDateAdd([Start];"8h";"24 hours")
    ProjDateAdd([Finish];"8h";"24 hours")

    But we need to add a condition to check if Day Light Save is Winter or Summer because then the difference is not 8 hours but f.e. 9 hours

    How can i do that?

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,233
    Location
    Unless the project takes place during the actual change over period the difference is still 8 hours from start to finish. You will normally just start and finish an hour earlier or later depending on which way the country adjusts its time clock.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Newbie
    Joined
    Feb 2022
    Posts
    5
    Location
    We want to have the Central European Times and the Indian Times shown in MS Project for each task. So we need to calculate from CET to IST but the calculation is different when in Summer or Winter DayLight Saving zone.
    I tried the below but that does not give me the correct result:
    IIf(([Start]>11-3-2023 And [Start]<5-11-2023);ProjDateAdd([Start];"3,5h";"24 hours");ProjDateAdd([Start];"4,5h";"24 hours"))
    It always gives the result for the last calculation meaning adding 4,5 hours.

    Testing shows me that this works:
    IIf(([Start]<[Current Date]);ProjDateAdd([Start];"3,5h";"24 hours");ProjDateAdd([Start];"4,5h";"24 hours"))
    Also this gives a "better" result
    IIf(([Start]<"15-8-2023");ProjDateAdd([Start];"3,5h";"24 hours");ProjDateAdd([Start];"4,5h";"24 hours"))
    The calculation gives below result but for 4-8-23 it is still wrong, where as for 15-5-2023 it works

    Start Date5 (the formula) Date6 (formula always add 4,5)
    4-8-23 08:00 4-8-23 12:30 4-8-23 12:30
    15-9-23 09:00 15-9-23 13:30 15-9-23 13:30
    15-5-23 08:00 15-5-23 11:30 15-5-23 12:30

    So i guess the issue is in the fixed date format that I use?
    Last edited by BVOPP; 08-15-2023 at 05:21 AM.

  4. #4
    VBAX Newbie
    Joined
    Feb 2022
    Posts
    5
    Location
    I got it... Thanks and sorry for the disturbance

    IIf(([Start]>DateValue("11-3-2023")AND[Start]<DateValue("5-11-2023"));ProjDateAdd([Start];"3,5h";"24 hours");ProjDateAdd([Start];"4,5h";"24 hours"))
    

  5. #5
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,233
    Location
    Thank you for posting the solution so others can learn.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Tags for this Thread

Posting Permissions

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