Define specific date range for horizontal axis in ...

[ler1995]

Hello!

I am wanting to add a DATEADD formula to my base to set deadlines for different tasks based on a hiring date. I've done this in a previous base, but have since lost access. Can someone help me with the formula I need? 

For reference, some of the formulas I need help with are:

Task: Email for photo, new hire survey, and short bio of new hire (-7 days before Hire Date)

Task: Assign desk space for new hire (-3 days before Hire Date)

Task: Give Uniform (on Hire Date)

Task: Collect how to contact employee while OOO (1 day after Hire Date)

This is my current formula:

IF({Task} = "Email for photo, new hire survey, and short bio of new hire", DATEADD({Hire Date}, -7, 'DAYS'))

When I add another {task} = ..... , it changes the format of the first deadline from a date to T00:000:00.OOOZ and then the next task's deadline just shows up as "true." When I reopen the formula, it looks like it changes itself from 

IF({Task} = "Email for photo, new hire survey, and short bio of new hire", DATEADD({Hire Date}, -7 ,'DAYS'), {Task} = "Assign desk space for new hire", DATEADD({Hire Date}, -3,'DAYS') to: IF({Task} = "Email for photo, new hire survey, and short bio of new hire", DATEADD({Hire Date},-7,'DAYS'), {Task} = "Assign desk space for new hire").

Any help on what I'm doing wrong here?

TIA!

[TheTimeSavingCo · Accepted solution]

Does this look right?

 

SWITCH(
  Task,
  'Email for photo, new hire survey, and short bio of new hire', DATEADD({Hire Date}, -7, 'days'),
  'Assign desk space for new hire', DATEADD({Hire Date}, -3, 'days'),
  'Give Uniform', {Hire Date},
  'Collect how to contact employee while OOO', DATEADD({Hire Date}, 1, 'days')
)

Link to base

[ler1995]

When I tried your solution, I got a date for the first one, and the others say #ERROR 

The "tasks" are the first column for each record in a single line text field. Not sure if that matters!

[ler1995]

Nevermind! Human error got me and I noticed that I hadn't entered the hire date field (face plam)

THANK YOU!!

[ler1995]

I do have one question about this - I noticed that it wasn't calculating correctly. ie it showed that Give Uniform was one day before the hire date, the survey and bio was 8 days before, etc. etc. Why is it off by one day?

[TheTimeSavingCo]

Probably a timezone issue?  Try displaying the timezones both in the formula field and the 'Hire Date' field to see if they're different:

[ler1995]

Yes, they are different. Thank you! How do I change this so they match?

[VikasVimal]

https://chatgpt.com/share/6778f5a9-c2d4-8008-9585-d2f9b170a7c1

Here's some help. And to bookmark for future.

[TheTimeSavingCo]

Try toggling 'Use the same time zone for all collaborators' on and selecting the timezone you need!