Working With Dates/Time
Nov 12, 2009
I have table which looks like this:
Name of School Time1 Time2 LoggedIN
2436 DAVIS JH 11/10/08 8:20 AM 11/10/08 8:30 AM 1 2436 DAVIS JH 11/10/08 8:30 AM 11/10/08 8:40 AM 1 2436 SVM JH 12/11/08 7:00 AM 12/11/08 7:10 AM 1 2436 DAVE JH 12/11/08 7:10 AM 12/11/08 7:20 AM 2 2436 CRAW H 12/11/08 7:20 AM 12/11/08 7:30 AM 2
Total Number of Columns = 4
First Column = School NAme
Second Column = Time 1
Third Column =Time2
Fourth Colum = Logged In
I want a 5th column where i can get the following result:
If time in 3rd column is betweeb 8am and 9 am and time in the 4th column is between 8Am and 9 Amthe thee corresponding row in the 5th coulmn should be 8AM-9AM
If time in 3rd column is betweeb 9am and 10 am and time in the 4th column is between 9AM and 10 Am the thee corresponding row in the 5th coulmn should be 9AM-10AM
Likewise for each hourly time slot of the day
The time columns are in the "DATE" Format!
The Purpose is finally to get the number of students logged in each hour of the day,and finally draw a usage hour graph by school and time !
View 9 Replies
ADVERTISEMENT
Jan 15, 2009
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
View 7 Replies
View Related
Jul 31, 2014
So I have two dates:
Opening Date: 29/07/2014 13:27
Closing Date: 29/07/2014 14:42
These are formatted in DD/MM/YYYY and HH/MM
I need to subtract the dates and find the time taken from the opening and closing time.
View 3 Replies
View Related
Oct 7, 2006
d9 is where i enter my start time from a drop down menu and
d10 is where i enter my finish time
what i would like to do is have a formula to work out my total hours work then minu 45min and tell me whats is remainig which is over time
example
i start work and 06:00 and finish work at 15:00 which is 9hours i then remove my 45min break which then give me 1/4hour (0.25 of an hour) as over time
the 0.25 is then timesed by a figure of say 13.4481 which would then tell me that i have earnt 3.362025
and so on eg if i have 0.5hour it would tell me what that work out as.
View 14 Replies
View Related
Jul 15, 2014
I have 4 distinct columns.
Adm Date Adm Time Trans Date Trans Time
1/16/2014 937 1/16/2014 1045
1/1/2014 121 1/1/2014 121
1/14/2014 800 1/11/2014 735
1/30/2014 100 1/30/2014 205
1/13/2014 800 1/12/2014 1202
I would like to calculate the difference (# hours spanned from the dates shown). Unfortunately when we transfer the data the COLON is dropped from MILITARY TIME so I am having problems in the calculation. This is also made more difficult as some of the calculates span over two dates.
View 5 Replies
View Related
Oct 22, 2008
This is for a VBA TextBox (Not Worksheet). I have two text boxes, both are going to have dates. Textbox1 a user will enter a date; example 10/22/08 or 10-22-08 or 10-22-2008 etc, TextBox2 will get that date and add seven days.
View 3 Replies
View Related
Mar 3, 2008
I have a spreadsheet with various date fields and next to those time fields. I need to calculate the amount of working hours between the 2 lots of values
for example
Cell A has "22/02/2008"
Cell B has "16:44:00"
Cell J has "25/02/2008"
Cell K has "08:59:00"
The answer i'm looking for is 01:29:00. The working hours are Monday to Friday 07:30 - 16:00. This is what I have so far and it gives me the total hours between the 2 dates but not the working hours. =IF(J10-A10=0,K10-B10,((J10-A10)+(K10-B10))) (Formatted with [h]:mm:ss). I have tried using workingday() but can't get the desired result
View 5 Replies
View Related
Mar 18, 2014
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
View 5 Replies
View Related
Oct 10, 2007
I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.
So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to.
Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.
What formula should I use or if someone could create an example that would be really usefull :D
View 12 Replies
View Related
Jan 8, 2009
I have the output of my database in an excel file and one of the columns is the date of instalation (of a PC in this case) and I am trying to use the date of instalation to calculate the date of RENEWAL (we have a set life cycle for our machines and so we calculate in months when its due for an upgrade).
I think just a simple formula will work. I have attached an excel file with some example information.
In column B I have the computer type(this will determine how long of a life it has), In column D we have the instalation date which is the starting date (the start of its life cycle). Here are the variables.
HW/PC DESKTOP = 1/7/5
HW/PC DESKTOP CAD SP = 1/7/3
HW/LAPTOP = 1/7/3
HW/LAPTOP CAD SP = 1/7/3
HW/LAPTOP HIGH-END US = 1/7/3
HW/LAPTOP ULTRA-LIGHT FR = 1/7/3
And finally, in column F I would like to display the life cycle it has and then in column G have the exact date of renewal.
View 14 Replies
View Related
Jun 25, 2009
I know I have done this in the past, but now I am having trouble. I have dates in cells that I want to do the following:
First Scenario:
If the date is today, change the color of the text. I have:
Cell Value is equal to =TODAY()
that is working fine
Second Scenario:
If the date is anything within 4 days before today, I want that to change. I have:
Cell Value is Is Less than =Today()<4
that is not working
Third Scenario:
If the date has passed and less than 4 days before todays date, I want something to change. I have:
Cell Value is Less than =TODAY()-4
But that is not working.
View 2 Replies
View Related
Jun 26, 2014
[Code] ....
In column F I have a list of dates (dd/mm/yyyy).
I can manually filter dates ok, but want to set it to part of code to use automation for a report.
The code when run, finds nothing, where it should. I tried using [CDate] also but also same result.
View 3 Replies
View Related
Apr 30, 2013
I want a list of working dates for the year in excel. Wanted to have it so that there was some flexibility in the table too, but cannot get it to work 100%.
For some reason in the below example, in December, 26th shows up (despite being in the list of holidays) and the 29th (Sunday) shows up too.
[URL] .....
View 9 Replies
View Related
Jul 25, 2002
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View 9 Replies
View Related
Apr 18, 2007
I need to work out the number of working days between 2 dates, where the working week is Monday to Thursday, ignoring all bank holidays etc.
View 2 Replies
View Related
Jan 10, 2014
time variation between 2 dates including time (Exclude weekends and holidays), but include the worked time of weekend, if the end date and time falls under weekend or holiday.
Eg., A issues reported on 01Jan2014 12:00:00 PM and Resolved on 11Jan2014 12:00:00 PM
There are 3 weekends in the above mentinoned date - 5th, 6th and 11th
I can eliminate the weekends using the networkdays formula, but however in this case I need to eliminate 04th and 5th Jan (weekends), but Consider 12 hours from 11Jan2014(which is also a weekend), as the issue got resolved on Saturday afternoon, assuming for critical cases team works irrespective of holidays.
View 1 Replies
View Related
May 5, 2009
I have a job tracker program that daddylonglegs helped me with a few days ago. I thought I would be able figure this out on my own but failed. I've attached the file to help show what I need. I know the final ship date of a project. Sometimes my projects need to go out for teflon coating.
I need Networkdays to give me a TO TEFLON date that is 5 days before the final ship date and factor in weekends and holidays.
View 2 Replies
View Related
Jun 4, 2009
I need a simply excel formula or macro which calculates the numbers of months i need to accrue for certain costs. The data I have includes the start month of the invoice, the end month of the invoice (i.e. showing how long the invoice is relevant for, quite often 1 year), and obviously the current month.
(eg. a 12 month invoice with a start date of Oct 08, end date Sep 09, current May 09 = 8 mths to accrue)
(eg2. a 12 month invoice with a start date of Apr 09, end date of Mar 10, current May 09 = 2 mths to accrue)
(eg3. a 12 month invoice with a start date of Jun 09, end date of May 10, current May 09 = 0 mths to accrue)
View 3 Replies
View Related
Apr 15, 2009
I have a spreadsheet stating a date that my company will deliver a product by but due to a set of certain circumstances we need to pull forward the dates by 3 or 5 working days. Basically it needs to show the dates below but minus 3 days unless this includes non-working days (i.e. weekends)
01/01/10
02/01/10
03/01/10
04/01/10
05/01/10
06/01/10
07/01/10
08/01/10
09/01/10
10/01/10
View 3 Replies
View Related
Jun 17, 2008
I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm [code]....
View 9 Replies
View Related
May 28, 2014
I am trying to group some dates in a pivot table.
Unfortunately I receive an error saying it can't group the selection.
I have done "text to columns" and made the cells - (date values) and I have had no luck.
As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......
I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.
View 3 Replies
View Related
Oct 30, 2009
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
View 9 Replies
View Related
Jun 17, 2008
I need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however
the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm
DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"
=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)
View 6 Replies
View Related
Aug 6, 2012
I want to create a formula that works out the number of days between two dates but excludes weekends?
View 3 Replies
View Related
Feb 5, 2009
I need to calculate the time spent replying to my inquiries: I log the time I receive my inquiry as: 06/02/2009 09:23:00 in column A. I log the time I send my reply as: 07/02/2009 07:23 in column B. In column C, I need to put the Formula that will return the following result: 0 days 22.0 hours.
View 5 Replies
View Related
Mar 22, 2007
I have 2 dates+ times.
1 the contract start date and time.
2 the contract end date and time.
Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00
This is 2 part days and over a weekend.
View 9 Replies
View Related
Apr 19, 2014
In Cell A1 I have the date. In Cell B1 is the shift. There are three shifts labeled as 1, 2, and 3. Is it possible to create a rule that will convert 1 to 0700, 2 to 1500, and 3 to 2300? Below is an example of how I need the shifts converted to time (military).
[Code].....
View 4 Replies
View Related
Aug 1, 2007
I have a spreadsheet that users input the temperature each hour, for a 24 hour period. The time used is 24hr clock, as example, cell A1 will be 03:00, and cell B1 would be the forecasted temperature.
On another sheet, the user inputs the temperature for the specific time a airplane is going to take off, i:e: 07:32. Is there a way I can have the second sheet look back to the first sheet and grab the temperature for the correct time (using the hour block it falls into). The first sheet time blocks are every whole hour, i.e 08:00, but on the second sheet, the time could be any hour/minute, 07:32.
View 9 Replies
View Related
Aug 28, 2009
I have a fairly complicated working time calculation I need to make, the sheet calculates how much 'actual working time' is spent on a product. 'Actual working time' means just that, breaks & any other non working time removed from the total.
My sheet set up is as follows:
Column Z = Start Date
Column AA = Start Time
Column AB = Finish Date
Column AC = Finish Time
Column AD = Actual working Time (hh:mm)
Column AE = Planned stop time (hh:mm)
Standard working times are:
Monday to Thursday 07:30 to 16:15
Friday 0730 to 1200
First tea break is 10:00 until 10:10
Lunch is 12:30 to 13:00 (except friday)
Last tea break is 14:40 to 14:50
1) Jobs can be 'in production' for many days, sometimes weeks
2) Overtime may be run before or after shift, also weekends
3) We can have 'Planned stop time' which will also need subtracting
View 9 Replies
View Related
Feb 24, 2014
1.The attached file shows an example extract of a data extract that has thousands of lines. See the Data Tab.
2.What I need is some time that has passed between two dates in a DDHHMM format
3.What I then need is the top 50 of each of the times (or the longest time past)
4.The column headers will be: WO Number | Contractor Name | Time Calculation – see the various Report tabs
5.The calculations in the Data tab are between the following columns.
a.Difference between Column Z and Column AC
b.Difference between Column AC and Column AD
c.Difference between Column AD and Column AE
d.Difference between Column AE and Column AF
6.Is it possible to show the top 50 only in time?
This report is forming a part of a larger report and all other formulas are already present so I am hoping to keep the file size quite small. The aim is to then hand this over to someone else to just print on a monthly basis depending on the data that gets added with minimum input.
View 2 Replies
View Related
