Date Calculations

Jester 1978

New Member
Joined
Sep 15, 2002
Messages
48
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Paddy
 

Jester 1978

New Member
Joined
Sep 15, 2002
Messages
48
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?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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...

Paddy
 

Forum statistics

Threads
1,143,733
Messages
5,720,553
Members
422,291
Latest member
Deveshk

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
Top