Tracking Log Calculating Time Between Email Notifications?
Apr 27, 2014I receive email, the forward information, then receive email from Help Desk that opens and closes the ticket.
View 13 Replies
ADVERTISEMENT
Automatic Email Notifications
Jan 3, 2009way to have excel shoot out email automatically? I would not know the first thing about VBA, so if it's a VBA code, please talk to me as if I were a 5 year old. I need excel to track dates in a column, and if they are within a certain time frame, i.e 3 months out from today, then excel should automatically send out a notification to the names associated with those dates informing them that they are due to complete that task before it expires. It should also inform their boss that they are due as well as myself as the tracker. Please, if you know the answer to this, word it as simple as possible.
View 9 Replies View RelatedTracking Date And Time Overlaps
Jun 18, 2008I'm trying to figure out a formula that would track a student's class schedule to see if there is a date/time conflict. I'm uploading an example of a ficticious student named Amanda for you to review.
According to the example, I should be prompted that there's a conflict between Amanda's first two classes because they both meet on Monday (M) and Wednesday (W) and the time during each of those classes overlaps.
This will need to be checked per student, based on their ID in column A.
Tracking Elapsed Time With No Date
Mar 4, 2007I have an assignment to audit the time it takes convert a printing press from one job to the next. I've been using a watch to take the readings in hours, minutes, and seconds.
I recorded them in my spreadsheet, as h:mm:ss, but it showed a date also. I think I finally got that resolved, but now, when I try to subtract one time from another time, I get an incorrect answer. I think I need to convert all of these times into decimals, as in 13.23.44, as oppossed to 13:23:44.
One post on page 1 of my search said to multiple A1*24, but I don't fully understand. I tried that, but 11:30 became 11.50?
I need to extract the set up time, or job to job changover time in a decimal format if possible. There are other tasks being done with those numbers later in the spreadsheet that are more conducive to decimals, than the time format.......
Tracking Call Handle Time Using Excel
Jul 28, 2014At my new job the CHT/CRT that we receive is three days behind what we are actually using. It makes it hard for me to keep track of my talk time, so I thought I'd use excel to track it. I can see the time I have been on each call through the day. I had to put it in 0:0:0 format to get it to average correctly. (I was trying to get it down to just seconds but that seems a bit complicated. I just then used "average=(a1:a30)" to average them together and it seemed to work a bit.
View 2 Replies View RelatedTime/date Tracking Only During Business Hours
Mar 19, 2007i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?
View 12 Replies View RelatedExcel Notifications
Aug 13, 2014I have a spreadsheet that contains all of our staff members (around 50 going down the "a" column) and the mandatory training that they must complete (10 subjects going across the top). They are a couple of things that I want to do with this sheet but how to do this.
1) I want the dates in which people's training expires to change colour to blue, those that is about to expire in a month to go green.
2) Copy the names and their training required onto another worksheet so I can keep a list of who I need to arrange training for. Rather than looking down each column and hand writing the names out.is it possible for the sheet to then email me people who's training has expired or is about too?
Example.
Fire safety First aid. Manual handling
John. 12/04/15. 23/08/15. 04/05/15
Frank. 14/05/15. 16/07/15. 08/08/15
Julie. 21/11/15. 11/02/15. 22/10/15
Sharon. 03/04/15. 07/06/15. 12/04/15
Calculating Time Taken And Average Amount Of Units / Time Taken?
Jun 11, 2014I am having a little trouble with a spreadsheet I am creating, the formulas and cell formatting I should be using to enable this to work.
Here goes:
in cell E3 I have a time started (e.g 12:45 pm), In F3 i have time finished (e.g 2:30 pm)
So, what i'm wanting is the time taken in G3 and also, i have the amount of units that is entered manually in H3. I would like I3 to show the time taken per unit.
Calculating Time And A Half And Double Time (After 40 Hours)
Feb 20, 2009I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.
View 4 Replies View RelatedCalculating Time Owed Based On Time Worked
Jun 23, 2009I know the title is a bit vague, but I cant think how else to word it!
I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.
I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
Calendar Event Notifications
Jul 31, 2008I have a calendar created as an excel workbook that currently has no functionality other than writing notes for specific dates. How can I get excel to show a message/popup/notification when the calender is opened up each time for what has been input as a note for the date today? The calendar has a year to view on one worksheet and then a month to view on subsequent tabs. please note i am a relative novice with excel.
View 14 Replies View RelatedCalculating Using Time
Nov 12, 2009Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View 14 Replies View RelatedCalculating Time In
Sep 9, 2009=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1))
i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error
so what im trying to do is base on the system time is if the person typed in
if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time.
6:55am gets $5
7:00am they get $5.00 for being on time
if they type 7:01am to 7:05am they get $3.00
if they type 7:06 to 7:10am would return You Were Late
after 7:10am would return No Pay
so Ex
D4 *answers in E4 *
7am would get $5
7:03am am would get $3
7:07am would get You Were Late
7:11am Would get No Pay
if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/
Calculating Time
Feb 21, 2010basically I need an excel sheet to calculate the period of time between cells.
For instance, in A1 I write:
18-Feb-10
In A2 I write:
13:00
Unfortunately I cannot write 18-Feb-09 13:00 in just one cell because it doesn't work.
Now, in B1 I write 19-Feb-10
and in B2 I write 15:14
Now we know that 26 hours and 14 minutes have elapsed between these two times. So if I want this to automatically be calculated in B3, what do I do?
Also, how do I apply the formula to all cells in column 3 so all I have to type in is the date (in column 1) and the time (in column 2)?
Calculating Time If Value Is Less Than Or Greater Than?
Mar 30, 2014I'm having a DICKENS of a time on this one. To explain, I have 3 columns where time is entered: a 'Time of Call', 'Time of Arrival', and 'Response Time'. The 'Response Time' cell autcalculates by simply subtracting the 'Time of Arrival' form the 'Time of Call' and using the difference to show the response time. To make time entry into the 'Time of Call' and 'Time of Arrival' simplier for my co-workers, I formatted those cells as 00:00 so that anytime you enter a number it will convert it to a time format (i.e., if you enter in the numbers 745, it will show as 7:45 in the cell). Because of this, when the response time is configured, it does it in hundreds instead of time format. So, if you enter in 7:45 as your time of call and 8:15 as your time of arrival, it shows 70 minutes as a response time because it sees the 7:45 as a whole number...745. Therefore, 815-745 DOES equal 70, but...that's not what I'm looking for. I want it too configure this as time...not as whole numbers.
View 2 Replies View RelatedCalculating Time That Is Goes Into Days
Apr 2, 2014I know how to calculate the duration elapsed between start and finish time. But how can I display the total time if for eg it's ends up being 300hrs?
I need the hours spent in the building per day and then the grand total per month.
Calculating Time Variance
Jan 26, 2009I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival.
I attach the workbook as am a bit useless at explaining myself....
What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14):
=SUM(E14-B14,H14)
This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.
Calculating Turn Around Time ..
Mar 11, 2009I've browsed the net searching for a solution to my problem and found one solution altough not entirely what i was after, but it was very close. It was provided on another forum, of which i'm not a member.
[url] ...
I have a TAT target of <= 2hours, however this is complicated by weekends, holidays and jobs received after working hours and on weekends. I've attached the file, which contains detailed information about my problem.
Calculating The Difference In Time?
Aug 17, 2013I'm calculating the difference in time:
9:54-9:45 = 0:09
Now to convert this in minutes (in number format), I multiply this number with 1440:
0:09*1440 = 9
When I click the cell this number is actually 9.0000005, rather than exact 9. I need it to be exact 9 since I have to match this number from other excel sheet.
To sum up:
This is what I expect:
31.99999 should be changed to 32
10.000001 should be changed to 10.
Calculating & Formatting Time..
Jun 2, 2009I am trying to track how long a macro takes to run from start to end.
View 4 Replies View RelatedLong Calculating Time
Aug 26, 2008I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.
{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}
But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?
Calculating Time Between Two Dates?
May 15, 2012I need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
Calculating Task Time
Mar 18, 2007I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
Calculating Flexi Time
Sep 15, 2007I have two questions about a spreadsheet where I am trying to calculate how much flexi time has been accured.
So far I have managed to do this: ....
Calculating Time Blocks
Apr 11, 2009I am trying to calculate the amount of time in blocks, using a pre-determined spread. For example:
AM block is anytime between 0600-1230
PM block is anytime between 1230-1830
Evening block is anytime between 1830-0000 and the Overnight block is anytime between 0000-0600
Sounds easy enough, yeah? Let me paint an example of how I want it to calculate though:
If an employee starts at 0600 and finishes at 1400, then the AM total is 6.5 and the PM total is 1.5.
If an employee starts at 1100 and finishes at 1900, then the AM total is 1.5, the PM is 6 and the Evening is 0.5.
If an employee starts at 2200 and finishes at 0800, then all of the 10 hours must report to the Overnight total, even though some of them fall within the Evening and AM blocks.
I just can't seem to get each block to show the accurate number of hours...and to bring in the rules that I require.
Calculating Value, Based On Time
Apr 27, 2004I am trying to write what I believe is a fairly simple set of calculations to determine non-qualified stock option values. Before this digresses into a discussion of the merits of Black-Sholes methodology, I must tell you that this is a "101" type sheet. I'm not an economist.
The deal is that my sister (for whom I'm constructing this worksheet), gets employer stock options which vest at different intervals.
In the past year, she's gotten some options that vest in two, four, and five year increments. I'm using Excel 2002, with the analysis toolpak, and stock pricing add-in from MS.
Have today's date, date of option grant, and can easily compute time (in years) until options are fully vested.
The trick is that options with a 2-year vesting period are 50% excerciseable in one year, 100% in two.
Four-year options are excerciseable 25% per year for 4 years; 5-year are 20% for 5 years, etc.
I'm trying to write a single date-driven formula to take each grant (individually), and divide it by the appropriate divisor (based on the current date), to figure out the current value.
I can't seem to quite figure it out how to write a formula which can handle:
Shares Strike Price Date of Grant Years Until Vested Value
100 26.57 1/4/2003 5
245 45.67 2/6/2001 4
Value should equal =
(if today's price is grater than Strike Price)
(Current Price * (Shares * (% vested which is based on "date of grant" + "years until vested")) )
-
(Strike Price * Shares * (% vested which is based on "date of grant" + "years until vested") )
how the math to make the "% vested multiplier" work...
Calculating Time All (redundant) Systems Down
Sep 20, 2013I have redundant system groups. One group may have (for example) two computers running. Another group may have eight computers running. But within any group only one computer needs to be running for the system to be operational.
I have a row with down start date/time (Column-A) and down end date/time (Column-B) for each downtime event. In then next columns I have the start times and end times for the other computer(s) in the group.
Finally, I have a date column to the right of all others (one row per day). Next to the date column I need to have the value that represents the common time all computers in that group were down.
I don't much care if this is done with a formula or by VBA. I can split the date and time values into separate columns and format the values however needed to facilitate the calculation.
I've placed an example workbook at the following location. [URL] ...
Calculating Time Elapsed Over Two Days
Dec 3, 2013I am trying to calculate the time elapsed. I have included a caption to show my formula. The problem I have is that some times are showing > 60 minutes instead of increasing the hour. When my day go over the midnight hour I get a negative number.
View 11 Replies View RelatedCalculating The Difference Between Time Stamps?
Dec 12, 2013I've got a spreadsheet of samples taken at certain time intervals for a period of 2.5 hours, they were collected in hh:mm:ss format. Most of them are approximately 5 minutes apart. What I'd like to do is convert the first time to 00:00:00 and then recalculate each sample from there. Is there an easy way to do this?
So, what looks like this right now:
8:45:00
8:45:36
8:50:36
8:55:36
9:00:36
Would look like this:
00:00:00
00:00:36
00:05:36
00:10:36
00:15:36
etc...
Calculating Difference In Time After Midnight
Dec 14, 2013I'm trying to calculate the available man hours I have for my shift/team. I have got the basic formula to deduct the time, and lost time for breaks ok, but when the shift end time crosses midnight the formula returns a "value" error message. i.e. start time = 18:00. End time = 24:00 breaks = 15 mins gives me a formula like this =TEXT(O17-N17-P17,"h:mm") and a result of 5:45
How I can make this formula work so it can calculate the variance even though the shift ends at 02:00am the following morning?