Converting A Date Based Upon A Time In Column A..
Jan 15, 2009
I some assistance with converting a date based upon a time in column A. I have a number of dates in column A as follows:
01/13/2009 11:50 AM
01/05/2009 09:14 AM
01/12/2009 05:30 PM
01/12/2009 02:30 PM
01/12/2009 01:45 PM
I need to convert these values to another date base upon the values in column A.
If the time < 2:00 pm then leave date as is, if time > than 2:00 pm then add one day. The expected results in column B would be as follows
01/13/2009
01/05/2009
01/13/2009
01/13/2009
01/12/2009
View 2 Replies
ADVERTISEMENT
Feb 21, 2013
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?
View 3 Replies
View Related
Oct 13, 2009
I'm trying to write a VBA code for converting the date/time as text to a date/time value.
The scenario is, I have three different types of text :
ddmmmyy
HH:MM
ddmmmyy:HHMM
I could have the code working fine with the first two types, but not the last one.
View 4 Replies
View Related
Jun 9, 2014
I have a list of dates stored via the following :
20100101 how can I convert this to date/time format to show 01/01/2010?
View 4 Replies
View Related
Mar 28, 2014
I have a database spreadsheet where it shows, the date and time a sale was made in 2 different columns, one for date, one for time (in Epoch format). What I need to do is convert this date/time to PST vs. the EST it's defaulted to.
View 1 Replies
View Related
Apr 1, 2014
I am trying to convert text in a cell from: 03/08/2014 2330 to a date that looks like this: 03/08/2014 23:30. Then I also need to extract just the time only and put that in a new cell.
I can do it in a few steps by using Data -> Text To Columns, formatting the date and time separately, and then putting them back together again.
Is there a 1-step way to do the original text to date/time conversion?
View 6 Replies
View Related
May 10, 2014
I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.
View 4 Replies
View Related
Apr 12, 2014
I am trying to find a way to have excel recognize text data input as date/time.
[Code] .....
Where 02 Is the Date, 2020 is the time (military) Z is Zulu/GMT, MAR is Month and 14 is Year. I believe excel recognizes
[Code] .....
But I have a spreadsheet (on a confidential system) with thousands of entries that I need to convert. Also, the people I have working for me are not remotely. I reject the idea of entering data that way.
I am also trying to keep the display the same format: ddhhmm"Z" MMM yy
View 9 Replies
View Related
Mar 9, 2009
I have column A set up to display dates in the following format: 03/04/2009. While the "look" is fine for the values in the column, I cannot get it to stay in that format when it is moved to a text file. It simply converts the display back to 39876.
I would like a macro that 1) looks at all values in column A and 2) converts each cell in the column to text format but leaves the contents so they are dates 03/04/2009 (mm,dd,yyyy).
View 2 Replies
View Related
Oct 9, 2013
I have a column that contains dates from our system that displays in "mmddyyyy" format (i.e., 10121999 or 8121998). How do I convert this to a 10/12/1999, 8/12/1998 formatted column?
View 1 Replies
View Related
Mar 22, 2012
Consider this code:
'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date
[Code] ......
This code checks the need for lights at a facility.
It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.
If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.
If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.
As I step through this code:
WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)
This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)
View 2 Replies
View Related
May 3, 2013
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
View 9 Replies
View Related
Dec 6, 2006
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View 9 Replies
View Related
Jul 11, 2014
A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)
I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.
[URL] ...........
View 2 Replies
View Related
May 18, 2003
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
View 9 Replies
View Related
May 18, 2003
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.
Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?
I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
View 9 Replies
View Related
Sep 3, 2013
* Need a normal date calculation according to selected time zone. * Formula required in [D7], facing problem in Negative TimeValue.
View 7 Replies
View Related
Feb 1, 2012
I have a chart that contains time per task and then adds the tasks for a grant time total.
Example:
0:05 wash hands
0:10 brush teeth
0:20 shave
0:35 - total morning prep time
In my chart the total time is currently 63:25:00 or 2d 15h 25m
I want to break this down into a number that can be compared to a work week. In other words, if a typical work day is 8 hours, can I break this down to show work days and remaining hours and minutes? I'd like it to break down as: ?days ?hours ? minutes where days is an 8 hour day, not a 24 hour day
View 5 Replies
View Related
Jan 20, 2009
I want to enter any thing in column (A) and then the date & time automaticaly put in column (B)
enter any thing in (A1), the date & time entered in (B1) automaticaly
enter any thing in (A2), the date & time entered in (B2) automaticaly
and so on ...
View 7 Replies
View Related
Jan 28, 2010
I have the a file that is downloaded from AS400. The time column is presented in 24 hours format in column D. For example : 92114 means 9.21 am. I need a formula to adjust the date in column B based on the following criteria:
1) If the time is after 2.00 pm, adjust to the next working day taking into account public holidays in column H
2) If the time is before 2.00 pm, remain the same day
3) if the time is after 2.00 pm and it is Friday, adjust to the next working day taking into account public holidays in column H
The correctly adjusted date is in column E.
View 2 Replies
View Related
Feb 21, 2014
How to identify the criteria (break or lunch) that he took first based on the time stamps. Here's the sample scenario: I would like to know which one is the 1st break or 2nd break they took based on the Date & Time criteria. See attached file for the sample data.
Sample results:
Agent 001 : 1st Break = 2/21/2014 10:13:00 AM
Agent 001 : 2nd Break = 2/21/2014 7:13:00 PM
View 2 Replies
View Related
Jan 8, 2009
If I use =now() in a cell it will enter the current date and time. For shift workers, this does not work.
I'm tring to figure out a formula that will only change the date at 6:00am every day. This way a person working the night shift will not see a change in the date on his sheet at all, only the day worker.
View 5 Replies
View Related
Jul 29, 2013
I am trying to do an analysis on 4 spreadsheets, each with approximately 4500 cells.
Each vertical cell represents a patient visit to a hospital. There is a timestamp, then a bunch of other metrics that I will be comparing. I want to sort the 4500 visits by time and date. This is how the timestamp of their arrival shows up:
Jul 02 2012 10:25 AM
^A single cell contains both the date and time next to each other.
I want to first separate all visits by time of day. I want to make one group that checked in between 10a and 10p and another for 10p-10a. Then I want to sort both groups in chronological order by date (i.e. starting with 2011 and ending in May 2013).
For one, I'm thrown off because the time and date are next to each other in the same cell.
View 4 Replies
View Related
Apr 23, 2008
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 Related
Jun 17, 2006
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?
View 3 Replies
View Related
May 12, 2014
I've attached an example workbook.
Trip Plan example.xlsx
I'm trying to work out how to have cells R11 - Rxx generate the correct date, based on the rest of the calculations.
Please note that the attached is a crude cut down version of the larger spreadsheet, for the purpose of highlighting my problem.
SO, if E7 is changed, subsequently the values in K11:Rxx will be respectively updated.
D12 and G12 are entered manually. (although I'll probably make these drop downs or date choosers at some point...)
Depending on the value in E7 and time in D12, the required break period is listed in K11: Kxx.
The time that these periods of rest are required to occur before is then calculated and listed in O11:Oxx.
I need R11:Rxx to calculated the appropriate date from these calculations, relative to the date in G12.
For example, the rest required in K11, is required on the same day, as such it need to populate with the value in G12.
However the rest required in K13:Kxx, is required by a time (O13:Oxx) on the following day - G12+1
In some case, this time variance could run over 2 or more days.
I figure it's got something to do with the value in D12 also including the date of 0/1/1900.
I'm happy to add hidden cells for formulas/results to work around it, as the end product will print as an A4 document, where many cells will be locked.
Would prefer not go with any VBA unless it's my only option as I haven't done any VBA stuff as yet.
NB (the value in P5 is static only in this example)
View 11 Replies
View Related
Jan 9, 2013
I calculate total labor hours entered into a CRM system and use it for various reports, but it's incredibly inaccurate for the following reasons:
Any labor put in between 6pm EST and 8am EST from Mon-Fri is considered "on call"All labor from Friday 6:01pm - Monday 7:59am is considered "on call"Labor entered on a company recognized holiday is considered "on call"Not every employee takes call
So, what I'm trying to do is take the total labor and pull out only the hours entered outside of the criteria above. If it were simply based on time during the week, i'd be fine, but I have no clue how to do everything else.
In the end, my new labor hours would be split into "Business Hrs. Labor" and "On Call" labor.
View 1 Replies
View Related
Mar 19, 2014
Looking for code that auto updates today's date in column C, when cells in either column a or b of the same row are edited or changed. I have never used VBA before, just browsing forums for something that might work. I think I'm close with this one, but maybe the offset is wrong - it populates the date in column b when a change.
View 2 Replies
View Related
Apr 19, 2014
I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.
I am using Microsoft Excel 2010.
View 3 Replies
View Related
Jul 8, 2009
I have a spreadsheet that automatically inserts the colon into time entries (2123 converts to 21:23), my problem is that the VBA doesn't recognize single minutes (i.e. when when a time is entered from 12:01am (0001 or 01) until 12:09am (0009 or 09)). When entered I get a "Run-Time error '5': Invalid procedure call or argument"
View 6 Replies
View Related
