Adjusting Date Based On Time
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
ADVERTISEMENT
Jul 5, 2006
I have a macro on a workbook that when I hit the button it asks for the date of a register sales journal that I want to import. Then it asks for the cash in drawer amount. Then it puts in all of the data into the sales spreadsheet. Now I want to use the on time method to bypass the button and the entry of the date, and even get rid of the cash in drawer. I want to make the macro run at 9:30 every night. I want it to use the date on the computer to tell it which date to find? Here is the code I'm using! button macro
' Button2_Click Macro
Sub Button2_Click()
ImportData
End Sub
Here is the main macro
Function FileExists(sFile As String) As Boolean
Dim iLen As Integer
On Error Goto NO_FILE
FileExists = True
iLen = FileLen(sFile) ................
View 9 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
Apr 6, 2009
This should be simple but I can't get it...any help appreciated. See sample attached
View 6 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
Sep 17, 2010
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
example:
if (b2<62) then price is ($sheet2.b2)
if (b2<124) then price is ($sheet2.c2)
if (b2<372) then price is ($sheet2.d2)
Is this even possible to put into a formula?
View 6 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
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
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
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
View 10 Replies
View Related
Mar 10, 2014
Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.
View 3 Replies
View Related
May 27, 2014
1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)
2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F
2=S-S
3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)
2=PM (15-2259)
3= MN (23-659)
View 9 Replies
View Related
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2
View 3 Replies
View Related
Mar 19, 2009
I am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.
View 5 Replies
View Related
Jan 2, 2013
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 Related
Mar 1, 2014
I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.
Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.
View 5 Replies
View Related
Oct 24, 2007
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.
View 12 Replies
View Related
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
Apr 22, 2008
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 Related
Jul 19, 2014
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.
View 7 Replies
View Related
Oct 20, 2009
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
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
May 21, 2008
I have a sheet with a list of dates in it that I wish to format to show the day as well as date and time. I am trying to do this automatically with a macro, below is the before and the desired after.
Before
Format - dd/mm/yyyy hh:mm
Appearance - 29/03/2009 00:30
After
Format - ddd dd/mm/yyyy hh:mm
Appearance - Sun 29/03/2009 00:30
This works fine when I format the cells manually, but when I try to format them via macro '29/03/2009 00:30' becomes 'Sun 29/03/2009 12:30' which is obviously a totally different time! Has anyone got ay idea why it might be doing this?
View 6 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
Mar 7, 2014
I have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
View 9 Replies
View Related
