# Date Calculations

#### Jester 1978

##### New Member
Hi from a newbie,
I have to create a report which is based on deliveries and despatch timescales. I need a way of working out if date A (the despatch date) is less than 2 days (ignoring weekends) than Date B (order date). or if possible working on the similar lines make sure the despatch date and time are no more than 48 hours from the order date and time.

if they go over the alloted time just need this to highlight (probably with conditional formatting)

Thanks
Jester

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### MrExcel MVP
On 2002-09-16 02:33, Jester 1978 wrote:
Hi from a newbie,
I have to create a report which is based on deliveries and despatch timescales. I need a way of working out if date A (the despatch date) is less than 2 days (ignoring weekends) than Date B (order date). or if possible working on the similar lines make sure the despatch date and time are no more than 48 hours from the order date and time.

if they go over the alloted time just need this to highlight (probably with conditional formatting)

Thanks
Jester

Hi - welcome to the board!

Given that:

a) date & time values are held as whole numbers & decimal fractions respectively,
b) conditional formatting responds to formulas that evaluate to true or false,

why not a conditional format with something like:

=(dispatchdate - orderdate) >2

#### Jester 1978

##### New Member
Thanks for that.

Any idea on how I get the info in the cell?

every time I have tried to subtract a date from a date I get a very different answer to what I expect!!

if I try just 16/09/02 minus 14/09/02, i was hoping that the 09 and the 02 parts would cancel out leaving me with a 2!?!?!

Any idea on how I could do that?

##### MrExcel MVP
Date calculations will only work as 'expected' if you (a) have the right expectations and (b) have the data set up right.

Re (a):

1) Read up on some of the relevant help files - type in 'dates' and 'times' - check out files like:
How Microsoft Excel performs date and time calculations
Tips on entering dates and times
2) Check out the sites below:
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm

Re (b):

Having read up on the above, check to see if you're data is set up right -

1) are your dates held as date values (check with =isnumber(), which should return true
2) Are your results cells formatted correctly (should be a number format, not a date format if you want the number of days as the result.

Post back if you need...

Replies
1
Views
191
Replies
6
Views
570
Replies
3
Views
255
Replies
1
Views
355
Replies
27
Views
633

1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

### 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?

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