Date Calculations

[Jester 1978]

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

 

[PaddyD]

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]

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]

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