Calculating waiting times

[Garry]

Hi Everyone

I am trying to set up a spreadsheet in Excel 2000 that allows me to input a
date that an item is requested (J4) and then keeps a record of the time
passed (L4) up to the present date until I input the date the request was
received(K4), eg


O
1
Todays Date

J K
L

4 Date Item Requested Date Item received
Length of Time

waiting for result

So what I want it to do is give me the days waiting between todays date and
J4 in L4 unless I enter the received date in K4 when I want it to give me the
difference between K4 and J4.

I hope this makes sense.

Yours

Garry

 

[Garry]

Hi Everyone

Sorry about how the post looked this is my first time I have used a forum
and the format didn't look like that whan I posted it

 

[Pete_UK]

Something like this in L4:

=IF(K4="",TODAY()-J4,K4-J4)

Format the cell as General or as Number with 0 dp.

Hope this helps.

Pete

 

[Sandy Mann]

I think that the answer I just provided to FXone will do what you want:

=IF(TODAY()<J4,"",IF(AND(K4<>"",K4<J4),"Error in
dates!",IF(K4="",TODAY()-J4,MIN(TODAY(),K4)-J4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Garry]

Thanks Pete, Sandy

It worked a treat. I have got another query so please forgive me if it
sounds simple I want the days waiting to be in red font if it goes over a
certain number of days thus bringing it to my attention to address, but once
I have received the result the font can go back to black. I can get the days
waiting to go to red if it goes over 7 days but I am not able to get it to
return to black font once I've put the received date in.

Yours

Garry

 

[Sandy Mann]

In Condintional formatting make the first condition with "Formula is:"

=K4<>""

and set the Font Formatting to automatic or black

and the second condition with "Cell Value is:" Greater then and enter 7 and
set the Font Format to Red (and Bold?)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk