7 Replies Latest reply on May 3, 2018 6:32 AM by Atanas Popov

# I need a a calculation using a date data field

Hello,

I need to create a calculated field that takes a Date data field and the days of the week and calculate based on that what date the payment will be received. More specifically, if the dates are between Monday to Thursday I need the calculated pay date to be +1 day, and if Friday or Saturday +3, and if Sunday +2. Is this even possible and if so how would i apporach this? Thank you in advance.

• ###### 1. Re: I need a a calculation using a date data field

Hello Atanas,

Use the below calculation to add the number of days based on the weekday.

DATE(IF DATENAME('weekday',[Order Date]) = 'Sunday'

THEN DATEADD('day',1,[Order Date])

ELSE DATEADD('day',2,[Order Date]) END)

The above calculation will add +1 day if weekdays is Sunday, in other cases, it will add +2 days.

Hope this helps.

S

1 of 1 people found this helpful
• ###### 2. Re: I need a a calculation using a date data field

Exact calculation of your requirement, replace the [Order Date] with your Dimension name.

Let me know in case of any issues.

S

2 of 2 people found this helpful
• ###### 3. Re: I need a a calculation using a date data field

Thank you Sudheer! This is very helpful! Just a little more to my question though. What if i have different Markets and the numbers will differ a for every market? Do i need to separate them in a different sheets and just duplicate the calculation ? i guess this would be the easiest since adding another date field to the if statement my cause an error. And, also for one of my markets I would have to consider after that everything after 2pm will be not +1 day but +2.

Thank you again for your quick response and help!

• ###### 4. Re: I need a a calculation using a date data field

Hello Atanas,

Different markets also the calculation will work. Coming to time, we need to follow the different approach. Can you upload a sample data or .twbx file to help?

You forgot to mark my answer as helpful / 'correct answer'. Please mark it Correct answer and helpful, so that others can also get benefited.

S

• ###### 5. Re: I need a a calculation using a date data field

Hi Sudheer,

I marked the answer as helpful.

Regarding uploading a .twbx file, unfortunately I can't since my company has a policy against that. However, I'm attaching a screenshot where you can see that I have used your formula but still the calculation is off for the highlighted items since it is supposed to be 2 days difference not 3 or 1 for Sunday from the delivery date.

• ###### 6. Re: I need a a calculation using a date data field

Hello Atanas,

Can you please explain how are you getting the "Weekday of Date"? if it is Weekday of Date field, please change it to Delivery Date.

Still, it is not working out I need to check the workbook. Open to connect offline!

S

• ###### 7. Re: I need a a calculation using a date data field

That was the issue yes. Thank you!

What is the approach if I am to use the time. For example, let's say for the Bulgarian market I would want all orders after 2PM to have new calcualted date of +2 days instead of +1?

Thanks again for all the help!