LinkBack URL
About LinkBacks
They are not pretty formulas, but you can find the one that I think will work for you at the top of this page:
http://www.cpearson.com/excel/DateTimeWS.htm
They are not pretty formulas, but you can find the one that I think will work for you at the top of this page:
http://www.cpearson.com/excel/DateTimeWS.htm
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I don't think Chip's formula will work when the ticket can be created at any time.
Can you clarify what you actually want to calculate, do you want to calculate the time and date that you must finish the job or do you want to calculate how many working hours have passed since the ticket was created (and thereby calculate how many hours you have left)?
The latter is easier to do.
What time is lunch?
i want to calculate how many working hours are passed and how many working hours are left to complete the ticket.
Suppose a ticket was created on 3/12/2008 10:00 AM and current time is 3/13/2008 10:00 AM so it should show "Age of Ticket = 8 hours" and "Time Remaining = 32 hours"
Lunch time is 14:00PM to 15:00PM
Last edited by amitmodi_mrt; 03-13-2008 at 04:58 PM.
OK, this formula is a little complex....
Assuming the ticket start time/date is in A2 and in C2 you have the current time [ use =NOW()] then use this formula in D2 for time passed
K2 should contain the weekday start time, i.e. 08:00 and K3 should contain the end time, i.e. 17:00
="Age of ticket = "&TEXT((NETWORKDAYS(A2,C2)-1)/3+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1)-MEDIAN(0,1/24,MOD(C2,1)-7/12),K$3-1/24,K$2),K$3-1/24)-MEDIAN(NETWORKDAYS(A2,A2)*(MOD(A2,1)-MEDIAN(0,1/24,MOD(A2,1)-7/12)),K$3-1/24,K$2),"[h]:mm")
Then for time remaining use this formula
=IF(MID(D2,17,5)+0>="40:00"+0,"Late","Time Remaining = "&TEXT("40:00"-MID(D2,17,5),"[h]:mm"))
Note: NETWORKDAYS is part of Analysis ToolPak add-in
What do you have in K$1 and K$2..?
The formulla is working but not giving exact result.. i put the start time as 3/13/2008 10:00 in A2 and =now() in C2 (My current time is 3/14/2008 3:33 AM, I am in India) it is showing "Age of ticket = 4:46"
however it should show "Age of ticket =7:00" as working hours ends at 17:00 PM.
=============================================================
I AM SORRY... IT WAS MY MISTAKE...
IT WORKED.. I DIDNT NOTICED WHAT YOU WRITTEN FOR K2 AND K3...
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU VERY MUCH......
Can i have your email id so that i can contact you if i need any help in future....?
Last edited by amitmodi_mrt; 03-13-2008 at 06:12 PM.
I prefer to keep any communication within the forum. If you have any further queries on this subject then just post another reply to this thread....or you can PM me if I don't respond
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks