VBA To Read Full Dates Not Just The Day
Aug 5, 2009I have a macro which searches for the latest date down a column and across particular rows. Extract of the code is shown below:
View 11 Replies
ADVERTISEMENT
Number Of Full Months Between Dates
Jun 8, 2007I want to calculate leave on the basis of Date of joining (DOJ). Rule is:
If DOJ is not he first day of the month, then no leave is earned for that month. Suppose DOJ is 26/12/2006, for Dec.2006 no leave is earned. One leave will be earned in January 2007 & subsequent months.
But if DOJ is 01/12/2006, then one leave will be earned in Dec.2006 & subsequent months also.
A zip file containg two excel files is attached for ready reference.
Count Full Weeks Monday Through Sunday Between 2 Dates
Jul 16, 2014Another thread that wanted to count "weekends" got me to thinking about this one:
Count the full weeks (from Monday thru Sunday) between 2 dates.
For example...
Data Range
A
B
C
D
E
F
1
Date
Weekday
------
Start
End
Weeks
2
8/1/2013
Thu
8/1/2013
8/15/2013
1
3
8/2/2013
Fri
[Code] .........
Full Monday thru Sunday weeks = 1
This array formula** entered in F2:
=(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
VBA Read Dates As Numbers?
Sep 23, 2009I have a userform that has several textboxes and checkboxes. Some of the textboxes read and display dates from certain cells. I have the checkboxes conditionally formatted based on the data in the textboxes. However it doesn't seem to be reading the dates as a value. Here's an example of my issue to make it clear for you:
txtBox 1 = 01Sep2009
txtBox 2 = 01Oct2009
What I need is for the checkbox text colour to change from black to red if txtBox1 < txtBox2. Here's the code I have in place:
File May Be Read-Only, Or You May Be Trying To Access A Read-Only Location
Jan 4, 2007I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
Separate A Full Name
Apr 25, 2009I have found these three different ways to separate a full name in say column A row 1. But when i insert different cells into the equation i get lossed. What is the proper way to separate a collection of full names into first middle and last. Do you highlight the list and put in one general equation or do you have to repeat these steps for each individual name in each cell?
First Name
=LEFT(A1,SEARCH(" ",A1,1)-1)
Middle Name
=IF(ISERROR(SEARCH(" ", RIGHT(A1, LEN(A1) - _SEARCH(" ", A1, 1)), 1)) = TRUE, "", _LEFT(RIGHT(A1,LEN(A1) - SEARCH(" ", A1, 1)), _SEARCH(" ", RIGHT(A1, LEN(A1) - SEARCH(" ", _A1, 1)), 1) - 1))
Last Name
=RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-IF(C1="",1,2))
Return Full Row
Apr 28, 2006I would like to enable my user to search on any one of about 4 columns, when the result(s) are found I need to return the full row to a result form. I've had no problems getting the information from the form to the worksheet but I've run into a few problems now. I should be able to be able to return the rest of the row with the result if I can determine a way to return the row number. Anyway these are the things that I need (in a nut shell):
1. Return a row number
2. Pass values to a form who should be able to change according to the size of the array
3. Have an array of variable size, with variable headers.
Calculate The Full WC Date From YY-WW?
Apr 30, 2009I have weeks in format YY-WW (this week is 09-18) and wish to calculate the full date for the week commencing day for that week. '09-18 is in cell S8. To work out the date I currently have: =DATE(LEFT(S8,2),1,RIGHT(S8,2)*7-8)-WEEKDAY(DATE(LEFT(S8,2),1,3))
Which I thought was working fine, but turns out to be the WC date for 1909. I'll probably have to change the -8, that's not a problem, but I can't work out how to get it to 2009.
Full Range Variable....
Jan 19, 2010how to make the full range variable...
View 9 Replies View RelatedReplaces The Full Date
May 12, 2006This formula looks at a cell and replaces the full date with jst the month
and the year....but some cells are blank, in this case this formula sends
back "100"
I need to add something to this formula that when the cell is
blank...instead of putting "100" as it does now, I need it to put
"Unscheduled"...
Like "If 100, then "Unsdchedule"
Only One Sheet Full Screen?
Jan 13, 2012I want only one sheet should in Full Screen Mode rest of the other sheets remain normal view(With Ribbon).
View 2 Replies View RelatedMatch Part Of Full Name
Apr 24, 2012I am looking to get a user's email address.
On Sheet A (Foreseen Absences) I have users enter their own data.
On Sheet B (email addresses) I have users' full names and email addresses.
I have the following formula entered on Sheet A to get the user's email address: =vlookup(C173,'email addresses'!A:C,3,0)
This works fine for staff who enter their full names, but I've got a few who are entering things like: G. Thoms or Marie which results in a #NA error.
Is there an easy way to update this formula to account for these instances?
Remove Full Row For Duplicates
Aug 19, 2013I have cells A11 to G250. In column E11 to E250 there are lots of duplicate product descriptions. Any duplicates in that column (E11 to E250) I want to remove that full row leaving just a single row (A to G) for that product removing the full row A to G for the duplicates.
View 2 Replies View RelatedFull Screen Crosshair
May 29, 2005This is a simple fine line crosshair which ALWAYS extends the full screen width and height. Excel users seem to be stuck with the little box cross. A full screen crosshair for Excel would allow one to instantly and easily see which row and column the cursor is on without clicking a cell or following from the cursor to the row/column with your finger on the screen. It may seem trivial, but once you have used a full screen crosshair, you just don't know what you've been missing.
View 9 Replies View RelatedInserting Users Full Name
Jul 12, 2006I am creating a userform and would like to be able to automatically enter the name of the person using it. The way our network is set up is Username = 1st 5 letters of surname + 1st 2 letters of forename. Due to this i cannot use
Function UserNameWindows() As String
UserName = Environ("USERNAME")
End Function
as this returns the login name as opposed to the full name. I also cannot use
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function
due to the fact that the office settings are defaulted to "Any Authorised User" unless manually updated by the user.
The users full name is displayed at the top of the start menu, but I have so far not been able to find a way to pull that into my form.
Document Load Up In Full Screen
Jun 18, 2007How do I make my Excel-document automatically display in full screen-mode when it is loaded?
View 10 Replies View RelatedIF Field Is Full Use Or If Empty I Need To Use Price
Sep 29, 2008Price - Bulk Price - Total price
And i need to calculate the total price (totalprice = (PRICE OR bulkPRICE * 2.5)
If Bulkprice contains a number i need to use that field but if it's empty i need to use Price.
Conditional Formatting - Full Row/Column
Oct 13, 2009If a cell in Column F = Closed Then, the entire row is filled green. If a cell in Column F = Open Then, the entire row is red with a strike through. I'm guessing this is simple, but I can only conditionally format on a cell-by-cell basis right now
View 2 Replies View RelatedRemoving Pence From Full Pound
Sep 1, 2013My spreadsheet has a column formatted as 'currency' (because it's a column of prices).
I need to have accurate values in the fields, including pence, eg. '£24.32'.
However the vast majority are round numbers with no pence eg. '£34.00'
So I am looking for a way to not display the pence when their are none, eg. '£34' rather than '£34.00', but I don't want to round to the nearest pound.
Replace All Text In Full Column?
May 22, 2014I need to replace a full column with a certain word. It needs to start at a specific cell and needs to replace the full column until it hits an empty cell. I can't do a find and replace because the words in that column are all different words.
For example:
I need it to replace all of column F starting at F2 with a specific word and it needs to keep replacing until it hits an empty cell.
Prevent ESC From Full Screen Mode
Feb 10, 2010I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes.
I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan.
Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
Prevent ESC From Full Screen Mode
Feb 10, 2010I have an excel workbook that I would like to
a) appear more app-like, and
b) show more data in the available space.
I do not want to go too elaborate by hiding (remembering and restoring) control bars and I reckon the View Full Screen is pretty much okay for my purposes. I am currently invoking View Full Screen on Workbook_Activate but hitting the ESC key returns to normal view. I can already see my users hitting the ESC key for various reasons and ruining my master plan. Is there are any way to prevent unintended ESC from Full-Screen or otherwise lock in the full-screen mode until revoked intentionally by macro (shortcut)?
Viewing Worksheets In Full Screen?
Oct 30, 2011I have large workbook with various sheets. In my first worksheet I have some command buttons that call each sheet as needed. However, I would like each sheet to be displayed either as a web page or in a way that the user does not see all of the menus, bars and so on.
View 1 Replies View RelatedVBA Code For Full Screen With Save As
Aug 21, 2013i have an excel workbook containing approx 20 worksheets. What I would like to do is make the workbook go full screen when the user opens the file and allow the user to save as into the same file path the workbook is stored when closed
View 2 Replies View RelatedSelect A File And Get Full Path
Jan 10, 2014I'm wondering what is the syntax to open a msoFileDialog box and select a file, resulting in setting a variable to the entire file path. If I use
msoFileDialogFilePicker
I'll only get the file name, without the path. I'd like to get the full path ending in the file name.
Populating Full List Of All Like (tyres)
Jan 29, 2014I am trying to create a spreadsheet the will populate/pull all tyre sizes that I have collected from different suppliers.
Tyre Size
165/80 R13
Size
Brand
Cost Price
Selling Price
Supplier
Cannt use Vlookup as it will list only one option.
Converting Day Of Month To Full Date
Mar 18, 2014I'm looking for away to convert the value of cells with a numbers that represents the days of the month as 1 - 31 to the full date.
As if the cell value is 3 convert to 3/3/2014 and so on. I tried changing the cell format which does covert it to 1/3/1900, then I experimented with DATEDIF but didn't get anywhere.
Creating Full Titles Using Table?
May 13, 2014I am trying to create a system to organize my company products. Up until now we had no part numbering system so I created one. The part numbering systems goes as such
Brand Code.Product Code.Bike Code.Color
These are examples
S7R.SF.101.Black
S7R.SF.101.Blue
S7R.SF.101.Gold
[Code]....
What I would like is to create a formula to expand the part number back to its full length name using tables on other sheets. I have tables for the brand code, product code and bike code. What I would like to have it do is Lookup the first part of the code "S7R" on a column from the following page and return the data from the cell next to it. Then continue on to do the same for the second part "cs" and the third"101" and so forth.
Basically the top example above would expand to be
Strada7 Short Folding Aprilia RSV4 Black
S7R = Strada7
SF = Short Folding Levers
101 = Aprilia RSV4
Black = Black
Full Screen Doesn't Work.
Dec 4, 2008This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View 9 Replies View RelatedField Contains The Full Pathname Of The Image
Jul 22, 2009I work for emergency services, and one of my colleagues has a GPS camera that snaps digital pictures and collects the coordinates where the image was shot. These coordinates are recorded in a database file readable in Excel. The emergency mapping software can load these points along with the images, but in order for the hotlinks to work (and thus be able to click on a point and load the image), I need a field in the database file that shows the full path name of the respective JPG image. As it is, the database file has a field (named ORIGINAL) that records just the filename of the image.
I need a field that contains the full pathname of the image. This is easily done in Excel, but I need an external script that will run this outside of Excel because I need to streamline the process. If dispatchers need to go into Excel and edit columns to make the data show up, they're not likely to bother.
I have a blank (not populated) column in the dbf (named INTERNAL) that I can use for the full pathname. What I want to do is begin with the text "C:/" and insert the text in the ORIGINAL column immediately following this. Is there a script, such as in a batch file, that will run this outside of Excel?