Date Calculations - (inc. Business days)

therock003

Board Regular
Joined
Jan 9, 2008
Messages
183
How can you calculate business days when making date calculations?

For example i set a date on a cell and i want to add 14 business dates.

How can i do it?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
its a strange function,it gives the resukt in days and you have to add date(1900;1;1) to the result in order to work.

Anyway one more thing.y does it start count from the next day?For example if you have a date 23/5/2008 and you add zero business dates it gives 24/5/2008.

And generally it seems to give one more day than it's supposed to.

Any thoughts on that?Do you have any experience with date calculation or did you find this one up?

Thank you.
 
Upvote 0
All you need to do is use the function and then format the result as a date.

What you are seeing is excel giving the result as a serial date value which is how Excel truly stores dates. By adding (1900,1,1) to it you are actually adding 1 day to the result which is why you are getting an extra day when adding 0 days to 23/5/2008 using the workday function.

For more info on how Excel stores dates and the date systems it uses have a look at the help and a topic 'About dates and date systems'.

Hope it helps,

Dom
 
Upvote 0
You're right i'm actually adding a date,i didnt think of that!And by changing the ype to date it's fixed.That's what i needed.Thank you for helping me.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top