Working With Dates And Making Calculations
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
ADVERTISEMENT
Oct 22, 2008
I set the time format to hh:mm:ss.000 to show also the milliseconds.
How can I get the milliseconds to another cell for making some calculations?
View 8 Replies
View Related
Jan 17, 2008
i have a due date column and an adjusted due date column. a proof is allowed to be held 3 days, if it is held longer, the due date is adjusted. for every day over the allowed days that a proof is held, we are allowed 2 more days of production. i want to create a formula that will calculate how many EXTRA days the proof was held, multiply that by 2 and then add that many days to give us the adjusted due date. is there a way to do that?
View 10 Replies
View Related
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
Apr 3, 2014
I want to make a single graph from two data sets that have individual dates.
help!.xlsx
E.g.
Data set 1
1. January 2010 2. january 2010 ... 10. december 2010 etc
10 15 ... 10 etc
Data set 2
1. January 2010 ... 1. January 2015 etc
200 ... 200 etc
View 8 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
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
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
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
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
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
Mar 2, 2010
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
View 9 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
View 9 Replies
View Related
Feb 9, 2010
if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.
View 5 Replies
View Related
Jan 27, 2010
I am making a spreadsheet that tells us the following information about prints that we do:
View 6 Replies
View Related
Feb 2, 2010
I have attached a sample file. All the data is inserted into this file via a text file, except one column "G". Someone in this forum was kind enough to help me in being able to include the city name 'G' to a parcel number 'A'. However, the page is constantly trying to complete 'Calculations' and won't let me do anything without first hitting 'Control Break'.
This is fine except that when I try to save the file into .txt, I don't have the option of 'Control Break' and I have to end up closing the file. As the 'real' file has over 100,000 rows, nothing happens very fast. I have tied changing the 'Calculation Options' but that doesn't seem to change anything.
View 7 Replies
View Related
Jul 11, 2012
Is it possible to have an input box for a range of calculations and then in my formulas set the range to anchor + variable ?
Something roughly like:
Range = 20
Product( F13:F & Range_Variable_Cell_Value)
View 2 Replies
View Related
