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 RepliesIs it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
View 3 Replies View RelatedIs there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist:
this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
View 9 Replies View RelatedI 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)
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'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?
I 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 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.
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
[Code].....
I work with alot of reports that are pulled directly from an application.
On each report i need to 'Alt=' at each cost center so that all manual changes are picked up.
However as you can imagine with thousands of rows this could take a while and be very tedious. Is there a quick way to do it?
Subtotals will not work because the costcenters will appear in the same column as the employee numbers.
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?
I import lots of data into a spreadsheet then have to manually format it which takes hours, I want code to automate it.
Example: I have data such as:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
12-----Bino------etc-----4--------6
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
I want it looking like this:
No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
shaded cells to here ishh 4-------7
No-----Name-----Date---Price----Cost
12-----Bino------etc-----4--------6
shaded cells to here ishh 4---------6
No-----Name-----Date---Price----Cost
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
shaded cells to here ishh 11-------11
The difficulty is due to the fact that the number in No column varies, for example there may be 2 rows of 10 or 4 rows of 10. What is the quickest way to format it all too, so have a border and font size etc all the same?
At present I have to manually insert blank rows between the different numbers in the No column, copy column headings, then manually sum up Price and Cost, then insert borders. But for hundreds of records.
I found this code to insert 3 blank rows manually for me when the numbers in No column are different:
What i want is for my user to click the save button linked to a macro that saves my file as Order0000. If there is a file already called this i want it to change the name to Order00001 and so on for Order00002.
Is this possible and if so what VBA code
I have created a userform which is initiated off a button in her spreadsheet, where if she enters an order number, it will display the values from the row containing that order number. I have made it so she can load from the excel sheet the values into textboxes, where she can change values then save them back into the excel sheet.
I have also include the ability to insert a new row at the end for a new order.
Everything works fine except the date values and I'm a bit stumped, I have searched but can't find anything that helps (I thought DateSerial helped but it didn't).
I've had two problems. the first was that the day and month swapped, I could load 1st Mar 2007 from the excel sheet and when stored back it changed to 3rd Jan 2007. This I presume to be due to US and UK date formats, I am in the UK.
I thought I had fixed this by changing:
oDisplay.Value = vStoreArea .............................
how do i use worksheetfunction save to save a particular sheet everyday at a certain time e.g. 5.30pm to a particular folder.
Also the name of the file will change with the day.. e.g. 22_Apr_07.xls for today.
And to also refresh the page / recalc before saving at 5.30pm...
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 RelatedI have a workbook with several worsheets in it. I wish to have the first worksheet as a list that will be hyperlinked to the other worksheets within the documant.
Is it possible to save the workbook so that it will open at the first worksheet every time irrespective of what worksheet you were on prior to clicking save.
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 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 have the below code to save a file once cretaed from master data, but it keeps failing on me.
View 3 Replies View Related