time formula =MOD(E5-D5,1) gives incorrect info

[kfromtexas]

Evening All!

Okay,

D5 E5 F5
time assigned time completed time worked

This is for a Hospital open 24hrs and to monitor the time an individual spent working on a help desk ticket.

Right now D5 shows 10:00 PM and F5 shows 1:51 but there is not a value in E5 so I do not understand why it looks like the ticket has been open for 1hr and 51mins. or why a value at all is showing in F5.


The formula works, but if the help desk ticket has not been completed and E5 is blank then I need F5 to show 0:00 or nothing.

Or .....a totally different option, I need like a timer in F5
that stops once E5 is given a value.

I am using 2007 any suggestions??

 

[Sal Paradise]

If you want time elapsed to continually update:
=IF(E5="",NOW()-D5,E5-D5)

If you want the time elapsed to be blank unless it is completed:
=IF(E5="","",E5-D5)

Edit: I do not use the MOD() formula because if a task takes more than 24 hours I don't think you want it to be listed as 0 hours.

 

[xenou]

IMHO, durations that have any chance of crossing a date boundary should be date-aware, or otherwise should take into account that possibility:

Start Time: 9/14/2011 10:00PM
Completed Time: 9/15/2011 1:51AM
Duration: (9/15/2011 1:51AM) - (9/14/2011 10:00PM) = 3:51:00

I don't really know why you get 1:51 in your original problem, as I get 02:00:00 -- but that's still not the right answer.

 

[Aladin Akyurek]

Evening All!

Okay,

D5 E5 F5
time assigned time completed time worked

This is for a Hospital open 24hrs and to monitor the time an individual spent working on a help desk ticket.

Right now D5 shows 10:00 PM and F5 shows 1:51 but there is not a value in E5 so I do not understand why it looks like the ticket has been open for 1hr and 51mins. or why a value at all is showing in F5.


The formula works, but if the help desk ticket has not been completed and E5 is blank then I need F5 to show 0:00 or nothing.

Or .....a totally different option, I need like a timer in F5
that stops once E5 is given a value.

I am using 2007 any suggestions??

In F5 enter something like:

=IF(COUNT(D5:E5)=2, E5 - D5 + (E5 < D5), "")

Custom format F5 as: h:mm.

 

[kfromtexas]

thanks everyone!

But i'm still having issues and need to explain much further in depth i think what i am trying to do might be too complex and its not as simple as i thought. i wanted to place my spreadsheet on here so everyone could see it but can not. first I have to do the html jeanie thing and saw the link on SAL's reply but in excel options:add ins: go: there is no little box for me to click to "tick" html jeanie option. GRRRRRR! im so frustrated.

I have been thinking of how to word this.

Sal's formula does work I see what he is saying, but it does not work the way i need it to.

if i enter that formula i can enter a start time right now and say that cell D8 was assigned at 1:00 PM and then F8 shows 4:40 because this ticket has been open/working on for 4 hours and 40 mins (because right now it is 5:40pm when I entered 1:00 PM into cell D8, so this is correct. so far this ticket has been assigned and opened for 4 hours and 40 mins. ... however, now it is 5:41pm and cell F8 still shows 4:40 ... so now this is wrong.)



I will be entering a time in cell D8 at the exact time the ticket is assigned.

If I need to look at this workbook in two hours from the time I assigned the ticket and entered a time into cell D8 then I need cell F8 to keep updating the time until a time is entered in cell E8.


If I went at the end of every day and entered data to store in this workbook this formula works. But I need it to be more of a interactive workbook. To where my manager at any time can pull this up and see how long,(how many days/hours/mins a ticket has been opened for.

So literally i need to create a timer in cell F8 that I guess some how coordinates with the time on my PC on the start bar.

I know this has to be possible because excel can due a calculation to automatically figure out the date when we enter =TODAY() and some tickets may only be open for minutes others may be open for days, so I need the formula to take this into consideration and it may say 76:34 to mean the ticket has been open for 3 days 4 hours and 34 mins.

I talked to a guy in my IT department and a friend and we are all trying to figure it out but obviously are not that advanced so..... who on mr. excel is the "ballin' brainiac who reins over us all" and can figure this out because i know many of you here are.... and I just do not get this.... in way over my head !


The reason I need this formula so precise is because then next once this cell E8 has a time entered in it I then have another fun formula to where I have to add all the time in column F but only when there is a time entered in column E and there is a certain name in column B so then i know how much time total was spent by that employee working on the completed tickets for this particular day. .....


Any help is so appreciated.... because then even if it is not right but close at least hopefully it can help me figure this out. every time I think I got it i realize why it won't work the exact way my boss wants it to.

Thanks and much love to you all!!!

 

[kfromtexas]

ALADIN! Yours looks pretty good the and i did not get hash marks for the ticket that was assigned over night and completed at 12am so this is good however, I can not just have the cell F5:F60 appear empty because the issue with this I realized is I have a formula where if a ticket is assigned and the status is "critical" than after the F5:F60 goes over 20 mins the F cell will turn red.

So at any given point through out the day my boss can pull this up see how many opened critical tickets we have how long they have been opened for and if they have been closed.

So if there are opened critical tickets that have gone over 20 mins then he knows he should pull up that ticket and look at it asap to get it closed.


So the thing is your formula I like i think i can make it jive but, the cells F5:F60 now no longer turn red like i need them to.

It's weird, b/c that was CF and I wouldn't think the way I have that set up would be affected. ????

 

[Aladin Akyurek]

ALADIN! Yours looks pretty good the and i did not get hash marks for the ticket that was assigned over night and completed at 12am so this is good however, I can not just have the cell F5:F60 appear empty because the issue with this I realized is I have a formula where if a ticket is assigned and the status is "critical" than after the F5:F60 goes over 20 mins the F cell will turn red.

So at any given point through out the day my boss can pull this up see how many opened critical tickets we have how long they have been opened for and if they have been closed.

So if there are opened critical tickets that have gone over 20 mins then he knows he should pull up that ticket and look at it asap to get it closed.


So the thing is your formula I like i think i can make it jive but, the cells F5:F60 now no longer turn red like i need them to.

It's weird, b/c that was CF and I wouldn't think the way I have that set up would be affected. ????

Try to run a formula in CF...

=$F5*1440>=20

Does this help?