I have a problem with my spreadsheet where i have needed to show over and under runs of theaters. So I have positive and negative times - which i solved using the 1904 date system calucaltion in the options menu.
But since changing this all my dates (in a seperate column) have increased 4 years and 1 day so the 3rd December 2012 is now 4th December 2016.
In my first column I will be entering both negative and positive numbers. In the cell next to it I need the amounts to all show as positive only. So both negative numbers and positive in the first column will all show as positive in the adjacent column/cell.
View 2 Replies View RelatedFor simplicity sake I will put what I have in close proximity cells and what my issue is. I am taking a number A1 (7.7) and turning it into time A2 =A1/24 (7:42)
A3 (18:00) Which is our work start time. I am taking 7:42 min estimated work day hours and adding that to our start time of 18:00 for A4.
A4 =A2+A3 (1:42) This tells me that we should get done around 1:42 am
A5 I enter the actual time we finished. Let's say (2:23)
A6 =TEXT(MAX($A$4:$A$5)-MIN($A$4:$A$5),"-H::MM")
This gives me an answer of (23:19), but if I type over the formula in A4 (1:42) which is the answer to the formula and already has that number there, I get the answer (0:41) in A6 and that is the answer I want. I can't figure out why I can't get A6 to give me an answer of (0:41) with a formula in A4. I even tried having another cell formulate A4 and then A4 =that cell and it is still the same.
I am doing math on times and want to be able to SEE values of Negative times instead of the ########. This is happening in a time card because I am summing the weeks total and subtracting 20 from it (expected 20 hours per week) so it will ALWAYS be negative until the last day of the work week.
View 4 Replies View RelatedI have a spreadsheet that I have combined the HH/MM/SS into one column and formatted it as time. If the numbers are negative then the - comes with it into the new column. I want to create a new column to calculate the negative numbers as zero and leave the positve numbers as they are.
Here is the formula that I used to combine the columns:
=TEXT(O322,"0:")&TEXT(P322,"00:")&TEXT(Q322,"00")
It is a formatting problem based on an INDEX formula i believe, but I am unsure where to start.
I have added a template where I have shown examples of what i want to achieve, the explanation is a lot clearer.
I have a list of data which contains the destination and departure time. In a cell I would like to type in the destination name and in the two adjoining cells I need the earliest and latest departure times to be shown. Example if I enter Carlisle then the results would be 18:20 and 20:55.
ToDepart
Birmingham20:35
Birmingham20:55
Birmingham21:35
Birmingham22:11
Bristol20:30
Carlisle18:20
Carlisle18:20
Carlisle18:20
Carlisle18:20
Carlisle19:30
Carlisle19:45
Carlisle20:15
Carlisle20:55
I am trying to import a .csv file with several fields that are timestamps. I get the file in Excel, and do Format, Cell, date as mm/dd/yyyy hh:mm.
My results come back as 1/0/1900 12:37:22 AM
My raw .csv field looks like 37:22.4
how I lost my date? I did check my system clock just in case, and it appears to be accurate.
I have a certain number of accounts for opening and close dates. What I wish to acheive is to fill a Range of date from the opening to the closing (or up to 200606 YYYY-MM) in another spreadsheet with the account number corresponding to that date next to it.
The VBA I have right now manages to create the list of accounts but up till 2006. It isnt taking into consideration the closing dates of accounts.
So For example What I want is to set up inside of the loop that to continue to do the loop until it reaches the closing date by YYYY-MM so if the clossing date is 200504 I will have data filled up to that date, and if the closing date is "" then fill the dates up til 200606. Ive tried out a number of ways of inserting the if statement but apparently the string create a problem.
Here is my current VBA and a Sample Sheet of what it looks like....
I'm using this formula to look up the date stored in one vile (shown as a number, e.g 35), and then pulling this back to the first sheet in order to convert it to a date. Problem is the date keeps showing up as a week after its supposed to be.
=IF(VLOOKUP(B76,'[MAD File.xlsx]Feuil1'!$A:$K,11,0)="","",MAX(DATE(BN76,1,1),DATE(BN76,1,1)-WEEKDAY(DATE(BN76,1,1),2)+('[MAD File.xlsx]Feuil1'!K74)*7+1))
I have attached a simple example worksheet showing what I am trying to do. I have a contract start date, a contract finish date, a notice period and a standard review period. To get a date when I should start my contract review I do the following: (finish date-notice period)- review period.
This is fine when the finish date is populated but if it isn't it results in a negative number. What I want to achieve is if there is not a finish date then leave the review date cell blank.
I have a file with two sheets.
Sheet1 Data are as follows :
Column A : Date (Data is : 31/01/2006,01/02/2006 ...... )
Column B : Name (Data is : ABC , XYZ, ABC .......)
Column C : Time (Format is : HH:MM) (02:00 , 03:00 .....)
Sheet2 Data are as follows :
A1 : Start Date and B1 : End Date
A2 : Name and B2 : Sum of Time
A3 : ABC and B3 : (i Want Result in this cell)
Result is :
I want Result in B3 cell : Name(ABC) of Time sum from sheet1 Column C between Start date(A1) to End Date (B1).
If you have some low time resolution data you need to compare with high time resolution data how do you average the high resolution data. For example data that starts and ends a fortnight apart and you want to compare it with data that starts and ends 1 hour apart you need to average the hourly data to do so. I can't figure out how:
So if I had Sheet 1 with hourly data
A B C D E
1 Date, Time, Measurement
2 01/01/2014, 01:00, 62
3 01/01/2014, 02:00, 38
etc....
Then in Sheet 2, fortnightly data
A B C D E
1 Date on, Time On, Date Off, Time Off, Measurement
2 02/01/14, 15:23, 18/01/2014, 12:25, 52
3 18/01/2014, 12:25, 01/02/2014, 14:19, 34
etc....
I'd want to add a new column of data to average the hourly data so I can use the correl function.
In one column I need dates and every date has to appear 16 times.
So, I need date 2009-06-03 in 16 rows, in next 16 i need next day - 2009-06-04.
I have A1= Date A, B1 = Time A, C1 = Date B, D1 = Time B, E1 = Hours between Date&Time B and Date&Time A
How can I enter a formula that takes the date and times from separate columns and calculates them?
I have a table that I am storing into an array. I have an array FDate where I store a date and another array FTime where I store times.
FDate is formatted as m/dd/yyyy Hh:Mm: Ss
Ftime is formatted as Hh:Mm:Ss
I know in excel that you can add and subtract dates and times. How do you accomplish the same task in VBA?
I'm trying to get excel to calulate the difference using the following format
dd/mm/yyyy hh:mm
dd/mm/yyyy hh:mm
So really I need excell to tell me what the actual difference is between dates
I seem to have the worst luck when it comes to getting dates and times to calculate together. My logic just seems to go straight out the window.
What I am trying to do, the end result, is get a Time Worked (duration) that I have spent on a ticket, and a Total Time, the time a ticket was opened, to the time it was closed.
I guess we can try to do this one step at a time.
The first thing that I would like to know is if there is a way to calculate Date/Time in a MM/DD/YYYY, H:MM format?
IE:
Ticket Opened ...................Ticket Closed
5/16/2008 2:54 PM..............5/19/2008 10:47 AM
Total Time in Days / Hours / Minutes
Right now I have the Date and Time split into separate cells, simply because I couldnt figure out a way to subtract the combined date and time.
I am trying to create a "daily donut" to track how long it has been since I left for my current deployment, and how much time I have left until I return home. I would like to show exactly how many year(s), month(s), day(s), hour(s), minute(s), and second(s) (like 1 year, 4 months, 4 hours, 5 minutes, & 34 seconds not 1.0 year, 0.33 months, etc.) it has been (again, not 3600 seconds, 60 minutes, 1 hour, etc.), and how many I have left. I have spent many hours trying to figure this out, and I have even tried looking through this forum, but I still can't completely figure this out. I was close in figuring out the date differences in the x amount of years, months, and days format, but it wouldn't account for the time. I.E., 06/03/09 05:00:00 AM - 06/01/08 06:00:00 AM, would show 2 days, instead of 1 day (I'm not sure how to incorporate the time yet).
The variables are:
A1 Date I Left For My Deployment in M/DD/YY HH:MM:SS AM/PM
A2 =Now() Function in Same Date & Time Format as A1
A3 = Date I Get Home from My Deployment in Same Date & Time Format as A1
Any ideas or help? And if possible, I would like to be able to do this using excel's built in functions (not excel add-ons, vb, etc.), so that it would work on government computers.
I receive data from a company I deal with that has the date and time of all of my transactions with them listed in an excel worksheet. Only problem is that the date/time that I receive is not in my timezone, which is a bit of a problem for me!
What I want to do is to be able to adjust the date and time shown in the cell forward by 14 hours, so that the data is shown in the timezone that I am currently in. So the end result for the first line of data in the attached file would end up being 2006-08-11,10:28:51 instead of 2006-08-10,20:28:51.
calculating project completion % based on Project start date and end dates .
View 5 Replies View RelatedIs it possible to seperate this sheet by Time - using column F, anything which is between the hours of 07:00 - 09:00 and 16:00 - 19:00? Response - column I - times up to 60 mins - 61 - 120 mins and over 120 mins
View 5 Replies View RelatedI am having difficulty finding the difference between two times and dates in Excel. I have researched this on Google and tried several formulas but none have worked for me. The format the data is currently in can be seen below:
A1: 1/3/2014 11:00:02.230 B1: 2/7/2014 08:42:37.637
A2: 2/12/2014 07:51:58.663 B2: 2/13/2014 05:36:31.893
(Note all times are military time format)
I want to display the difference in C1 in a format along the lines of "dd hh:mm". The data for seconds and fractions of a second is included in the source data but is not needed in the final results.
If the date and time falls on the same day, I have no trouble doing a simple "B3-A3" to determine the answer. For example:
A3: 2/5/2014 09:56:06.273 B3: 2/5/2014 10:39:51.34
Gives me an answer of: 0:43
Trying to do the same for A1 and B1 gives me this: "03 21:42" when I format it to "dd hh:mm".
Is there a formula that would give me the correct answer in both of these cases?
I need help deleting rows which contain a cell with date / time in the format dd/mm/yyyy hh:mm if the value is older than the current date / time
any ideas?
I need the difference between two dates/times field in hours or minutes.
Eg:
A1 B1
1/4/05 10:00 2/4/05 14:30
The result should be 13.5 hours, considering only 8 hrs per day, only business days and 8 to 5 workday.
I'm not too hot with Excel formulas but I'll explain as best I can. I have 3 columns with dates and times
A - 01/08/2008 08:17:08
B - 01/08/2008 12:17:08
C - 01/08/2008 17:00:11
This is what I want to do...
If A > B then Calculate the time difference between A and C
If A <= B then Calculate time difference from B to C
I'd like to get this formula in 1 cell if possible?
I can’t figure out why this formula is not working. I am trying to alter it a little, but I still think it should work. Here is what I am trying to do. I am looking to calculate the time difference between two work projects, but exclude time when the office is closed. So someone starts a project at 2pm and finish 10am the next day it will show a result of 4 hours because the office closes at 5.
Here is the formula. It is the time formula from cpearson.com ...
From the below example, I would need to know the difference between date & time of Received and Resolved in number of days. Meaning - time difference should also be calculated in days
CELL ACELL BCELL CCELL DCELL E - REQUIRED OUTPUTReceived
DateReceived
TimeResolved
DateResolved
TimeTurn Around Time - IN DAYS20-Jan-122:43:00 PM23-Jan-124:50:00 PMDifference between Resolved date & time and Received date & time
I get a list of data through xml data import
column A is dates WITH time, appears like so: 2013-07-13 20:25:02
column B is the product name
column C is the quantity
now I want to search certain items sold between two dates AND times which will then give me the total amount of that item sold between those dates and times
now I know how to do this with date but have a problem doing it with dates and times
I have dates (column E) and times (column F) currently in the format mm/dd/yyyy and hh:mm .
However, my formula in excel deals with dates and times in the format mmddyyyy and hhmm. How do I change the values of the dates to exclude the "/" and the times to exclude the ":" operators?
I want to change the actual value of the cell too. Basically, using custom format mmddyyyy in the date column will not work (because the true value of the date will still yield mm/dd/yyyy.