From an outside source, a date is brought into one cell, and time is brought into another. Is there a way to combine them into one cell with the format of m/d/yyyy h:mm?
I tried concatenate, but that did not work.
I have tried several ways to calculate the difference between 2 dates/Time fields. Here is what i have tried and the issues i am encountering.
Cell F3 Has the Create date and Cell G3 has the resolution date and i need to calculate the difference between the 2 in months, Days, Hours, Mins and seconds:
1st option - =G3-F3 and set the format of the cell to mm "m "dd"d" hh"h "mm"m "ss"s "
The problem is, for some reason the months isn't calculating correctly and appears to add 1 month
2nd option - =DATEDIF(F156,G156,"y")&"y "&DATEDIF(F156,G156,"ym")&"m
"&DATEDIF(F156,G156,"md")&"d "&TEXT(MOD(G156-F156,1),"hh""h ""mm""m ""ss""s""")
This appeared to work however because it looks at the date and time separately, when there are 22 hours difference which spans over 2 dates ( Created 13/06/2013 10:30:00 Resolved 14/06/2013 08:34:00) its shows as 1 day and 22 hours which isn't correct.
I want to add hours to a date-time cell to get result in date-time.
Format of cell A1 is d/m/yy h:mm AM/PM
Format of cell A2 is General
Format of cell A3 is d/m/yy h:mm AM/PM
I want to add A2 (number of hours) to A1 to give A3.
The formula I used is A3=A1+Time(A2,0,0)
The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.
I need a formula to add just the time to ' date and time', ignoring weekends.
eg:
Fri 24-Aug-07 10:52 is the date and time
28:48:00 is the time
If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40
But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
I've tried a number of examples but I can't seem to get it to work. the desired results in column C.
10/17/11 12:00 PM10/17/11 1:00 PM1:0010/17/11 12:00 PM10/17/11 9:00 AM(3:00)10/17/11 12:00 PM10/16/11 9:00 AM(27:00)
I have a problem concatenating time in excel if it is of Date + Time format..
What formula do I use to just add the time by, say, 3 hours? I'll need to use, say cell B2, which will add 3 hours to it.. What formula do I use?
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
View 9 Replies View RelatedI am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?
View 14 Replies View RelatedI have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
View 5 Replies View RelatedI've been reading up on Excel's date and time functions and can't really figure out the best way of doing this.
I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.
I have some cells which must be in the format 15/06/2007 15:25
I then need to add either days, months or years onto it.
Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
I need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings.
Sample attached. Sample rankings.xlsx
I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day
Count of Posts
Sum of Impressions
Impressions Per Post
Monday
52
1,881,468
[Code] ........
i need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings. sample attached.
View 3 Replies View RelatedI have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:
A1 - A4 have "totals" derived from an external source, and the fluctuate daily.
I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.
I've started with a macro that I would attach to BeforeSave as follows--
I am replicating a Matlab program which calculates tide levels at different times of day. I need to replicate it in excel to speed up data analysis and I am nearly there.
what happens is I need to apply a time offset to the time of high tide at port a based on the time of day, so if it is:
between 00:00 and 06:00 the high tide at port b is 81.6 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 74.56 minutes after the peak at port a
between 12:00 and 18:00 the high tide at port b is 81.75 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 79minutes after the peak at port a
I tried this formula, where CO2 has the time/date of the high tide at port a:
=IF(CO2<0.75,IF(CO2<0.5,IF(CO2<0.25,CL2+(81.6/(24*60)),CL2+(74.56/24*60)),CL2+(81.75/(24*60))),CL2+(79/(24*60)))
The problem is the high tide on 07/01/2005 07:45 is read as 38359.32 rather than 0.32 - is there any easy way to tell excel I'm only interested in the time not the date? I have this spreadsheet setup now to do all the other bits required and i is just the timing that is a problem.
I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
View 4 Replies View RelatedIn the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
I have two sets of data, first set contains records from a timesheeting system, including:
Name Date
Where each name will be repeated mulitple times each day worked.
Second set of data relates to the invoices for the same resources, and includes:
Name Invoice No Date From Date To
And may include multiple invoices for the same resource.
What I'm trying to do is determine for each record in the timesheeting system (adding a column next to this data) what is the associated invoice number? A simple VLOOKUP returns the first match it finds, however if there are multiple invoices for the same person, I want to return the correct number based on the date fields. I don't want to count the number of invoices, I want to return the corresponding invoice number that relates to the day.
I have a column of values resulting from subtracting a static date and time from the current date and time.
This means it is constantly updating, which makes it impossible to sort.
All my work depends on sorting those values, though.
I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:
01/01/13 00001
No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
View 2 Replies View RelatedI have a started time of say 8am from cell B3. I want to know what time it will be when I add a full number (hours) from another cell. For example A3+B4 where A3 is 8:00 A.M. and B4 is 4.04. I'm looking to get the a result of 12:04 P.M.
View 4 Replies View RelatedI am using the standard validation from the tool bar. What I am trying to do is in
check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"
Spreadsheet example attached.
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
I am entering data for ships arrived every month. There are about 200 records to be entered and for every record there are 4 date fields.
My question is : Is it possible to set default month (for ex = July) and year ( for ex = 2013 ) in these 4 cells so that
If I just type 16 it should be 16/07/2013. Because all my dates will be within that month July 2013.
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
View 5 Replies View Related
