Look at spreadsheet, calculating time off.
My problems are in columns (k) total amount of days off, (l) weekday days off, and (m) weekend days off
Columns n & o are the results that my employers computer delivers on that amount of time off!
I have provided at the end off my spreadsheet data, some info on the rules that govern how this is calculated.
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
I'm setting up a shift roster and need to determine whether a given time is
between the start time and end time of a shift where the shift runs across 2
calendar days
eg start 23:00 finish 06:00
I 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.
I am looking for a formula that will tell me how many days in each month for which a given time span is active. For example the time span 1/1/2014-2/28/14. I want to know the time span is active for 31 days in January and 28 days in February. Another example: time span of 1/15/2014-2/28/2014. I want to know that the time span would be active for 16 days in January and 28 days in February. I am looking for a formula that will capture the amount of days in each month for which the time span covers. as I only know how to write a formula with multiple if statements that require a different formula for each given month, rather than one formula that applies to all months.
View 6 Replies View RelatedI 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 RelatedExcel VBA Macro.
I need to convert a decimal (0.602083333) to Time (14:27) but how to do this.
I would like to do this in a VBA Macro, to add to my existing code.
I have two sets of data that I would like to be compared over a 4 day period.
One set (Mill data) has an associated date and time (29-Jun-08 00:42:02 etc.), and has a value generated every 4 to 6 minutes.
The second set (Dump Grades) has an associated time only (13:24 etc.), and has values generated at random time intervals throughout each day.
The first issue is that I must assign a date to the Dump Grades data, which I just can't seem to do.
The second problem is that I don't know how to get all of these data points graphed on the same graph, as the date and time thing is throwing me for some reason.
My data right now is arranged in columns on separate sheets, organized by dates.
I am trying to calculate the duration between two times on different days.
For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00.
I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.
calculate the running total of time spent in the attached spreadsheet.
View 8 Replies View RelatedI am trying to count the number of orders written per day. I used the following formula on another sheet and it worked fine, however on this one, A8 is a date only, and F2:F2000 is a date and a time.
I tried reformating the cell for date only, and it displays only the date, however, the time information is still stored, and I can't get it to do the count. Is there a way to count the date regardless of the time?
=COUNTIF('ACT Legacy'!$F$2:$F$2000,Stats!A8)
I'm building a schedule in excel and am having trouble with the calculation of total hours when lapsing a day, or more specific, adding it into my current equation. For example; Monday they clock in at 5pm and work till 1am. I have found a couple of ways to get the desired out come from this on it's own. I just add the value of a day if the out time is less than the in time, or if the out time minus the in time is less than 0.
However, I need to add this calculation into an equation that is already calculating time elapsed within a single day, turning that into a decimal number and then checking if the total time elapsed is less than 6 hours, if false it subtracts a thirty minute break. Here is the current equation I'm using, I'm sure that there is an if(or, or if(and I could use to make it work when the time lapses 12am while also performing the 6 hour check to subtract the break.
=IF(((F7-INT(D7))*24)-(D7-INT(F7))*24
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
123 ABC 07/01/2009
123 ABC 07/02/2009
123 DEF 07/02/2009
123 GHI 07/02/2009
123 ABC 07/03/2009
123 ABC 07/07/2009
123 DEF 07/07/2009
123 ABC 07/08/2009
123 ABC 07/09/2009
etc....
Should return 4 work days for employee 123 over the date range 07/01/2009-07/07/2009
I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet
is opened, based on the current date, to calculate how many years, months and days
have elasped yy mm dd since the start date...
My date formula knowledge is a little limited.
I have a date in col A and a time in col B. I'm trying to use an if function so that if time in Col B is greater than 15:30, the date in col A is returned plus one day. If time in col B is less than 15:30, date in col A returned with no change.
View 4 Replies View RelatedI have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies View RelatedI have got a project that has several hundred starts and finishes. I want to count them over time to use as an indication of progress. ( I want to show this graphically)
I have got the graph working but it is pretty long handed.
I have tried using a pviot table and picvot graph which almost does it but has a few problems (if there is no data in a month then it wont create an entry for the month, and the cumulative over time is not quite working).
Both examples are in the attached file : Progress Curve ozgrid1.xlsx
I am using the following formula in Column E to calculate the difference between an employees start time, and their previous shift end time in order to work out how many hours rest they have had:
=IF(A4=0,"",IF(I4=0,"",MOD(I4-B4,1)))
My Current Formula works fine for same day calculations, but if an employee finishes work at 18:00 on Friday and starts work at 22:00 Saturday night, Excel calculates the Total Rest hours as 04:00, when in fact they have had 28:00 hours rest...
Is there a way of calculating the hours difference between two dates?
I have attached a sample of my spreadsheet to illustrate
I have 2 production unit : A; B
They make payments to suppliers on certain periods : p1, p2..
The payments are on variable and different currencies : EUR, SEK, USD, CHF,AUD..
I explained the sample case table in attached excel sheet. My question is "how can i create the summary table easily and quickly by excel?"
currently I am calculating it by a simple excel formula (vlookup) that I need to change the range for each time. It s very time consuming and easy to make mistakes..
I work only on Wednesdays and Thursdays. I'd like to calculate the number of my working days between two dates.
NETWORKDAYS seems like the right place to start, but I can't think of how to tell it to ignore Mondays, Tuesdays and Fridays as well as the weekend days.
I am developing a report that tracks the efficiency of industrial sorters on
an automated assembly line. The line runs around the clock in three shifts
(1: 7:30am to 3:29pm, 2: 3:30pm to 11:29pm, and 3: 11:30pm to 7:29am), and
collects certain performance indicators, such as units processed. I am
trying to calculate the units processed during each shift on each day. For
example, if a run ran from 2:30 pm to 4:30 pm, and processed 100 units, I
would want 50 units credited to shift 2 and 50 units to shift 3 (we are
assuming a steady rate of processing).
My data arrives from the machine with the following columns (each run is a
separate row):
B|D|E|G|H
Run #| Start Date| Start Time| Units Processed | End date | End Time
I have successfully used the start time to determine the starting shift with
IF statements:
=IF(--E3<0.3125,3,(IF(--E3<0.6458,1,(IF(--E3<0.9792,2,3))))) and a similar
formula to calculate the end shift. This works fine if the run only spans
two shifts within the same day.
My problem is that some of the runs, run across more than one shift and even
across days. For example, a run that ran from 10:30 pm on one day to 4:30 pm
on the next, would overlap 5 shifts and two days. I need to be able to
figure out the proportion of time spent on each shift, and use that to get
the proportion of units processed during each shift on each day.
I am currently thinking of a convoluted series of nested IF statements (and
generating lots of columns to the right of my data for each potential shift
within a run), but this seems inelegant and cumbersome. Is there a more
logical way to approach this? Even more specifically, is there a function
that can pair my start/end dates and times with a set of shift start/end
times and calculate the elapsed time for each shift within a run? Something
with MATCH or VLOOKUP maybe?
I'm looking for a formula that will return the time left to a birthday, in Month and Days.
View 5 Replies View RelatedI have a cells with a date and time in each cell. I want to subtract the number of days between the cells only. Is there a way to do that with the time in the cell? If not, how do I remove the time in each cell?
View 5 Replies View RelatedI have been trying to write a formula to calculate the differrence in days, hours or minutes between 2 cells in Excel.
Here is an example of the two cells
06/08/2011 00:0006/08/2011 12:19
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like
Column A agent name
Column B agents start time
Column C agents end time
Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
I have a column of tasks that take a certain amount of time to complete formated as h:mm:ss. I want to total the column and convert the total to days, hours and minutes. Is that posible and if so how do I configure a formula and format the cell? example:
task 1 54:00:00
task 2 20:45:00
task 3 27:05:20
task 4 51:10:45
total 153:01:05
How many days, hours and minutes?
I am trying to enter 00:00:00.031, but it is changing it to 00:00.0. Can someone please tell me how to stop it from doing that?
View 9 Replies View RelatedI have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2