I have a column where the data is listing the day of the week and time worked. What I need is to sort the column by the first four letters in cell I used the left(e5,4) to return the letters in a helper column but when I sort it still not in the Monday -Friday order. example:
View 2 RepliesI am trying to sort some data which contains numbers and letters and can't seem to find out how to personalise the sorting function on excel. Is it possible to sort cell with a mixture of letters and numbers?
Here is a link to a file to show how far I have got! [URL] .......
I have an excel database that contains a code to identify specific people.
NAME ADDRESS PHONE CODE
Jones 3 Quay St, PN 063586954 JU79N4
White 24 Dyk St, PN 063547786 9GVJ64
Smith 9 Random St, PN 063512698 4LN867
Butt 89 Yeah Pl, PN 063569986 D920HK
Handle 69 James Ct, PN 06 3549687 ZK26S84
If I wanted to filter the list so I only had codes that had Z, N, H in it. How do I do that??
TWO more days to turkey day. 3 more to BF --- yaaaaak! Anyway,
Iam trying to display only 3 workdays in each of three cells. Days displayed depend on today's date.
I am usinf the following but it displays SAT and SUN.
Q3 = today's date
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays SATURDAY -- would like it to display MONDAY
Q3 changes to reflect today's date
therefore this would display:
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays MONDAY
In other words I want to display only the week days in these 3 cells.
I tried:
=TEXT(WEEKDAY(INDIRECT("Q3")),"dddd")
but "Q3 + 2" will not work
the end result desired:
MONDAY
TUESDAY
WEDNESDAY
TUESDAY
WEDNESDAY
THURSDAY
[Code] ......
I have the followinf formula
=IF(A2="","",IF(C2>TODAY(),"",IF(E2="",IF(B2="1st",WORKDAY(A2,4,$H$1),WORKDAY(A2,5,$H$1)),C2)))
What I am trying to do is cause (where the formula is) to:
If A2 is empty, put nothing,
If C2 is greater than today put nothing, otherwise put C2
If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.
I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
I have a code that I have put in the ThisWorkbook section, and I thought it should work - but nothing happens....
Code:
Sub FirstSub()
Application.OnTime TimeValue(Sheets("SetUp").Range("G1").Text), "SecondSub"
ResetTime = Now() + 1
Application.OnTime ResetTime, "FirstSub"
End Sub
Sub SecondSub()
If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call CreatePDF
End Sub
It's supposed to call a sub called CreatePDF.
I need a function that recognizes the first and last weekdays (M-F) of the month. I would prefer to do this with a function and not VBA.
View 9 Replies View RelatedI have a sheet with alle days an date in one month
in column "A" the date (format: TTTT TT.MM.JJ)
in column "B" I have the worked hours
Below in the sheet I would to have to sums
each one for weekdays and weekends,
because my guys get more money if they work on a saturday or sunday.
I have been playing with the formulars
=sumif(weekdays(....., but cant geht the right thing
I would like to have a spreadsheet where everyday of the month dislpays automatically
such as
01/01/08 in A1
01/02/08 in A2
etc.
According to a cell name Account Period (eg 200801)
But I don t want week end days to be displayed. In the same excel document, I have a sheet with all non working days and week end for 2008 and another sheet.
I need to run job between 4 July 2012 and 1 September 2012 but only Monday through Friday.
I have used weekdays() to find the name of dates between these two dates but need to narrow to Monday through Fridays.
I have done a V-lookup that auto populates my project report doc with different lead times/dates depending on the product selected. Problem I have it that the dates being pulled through are for all calendar dates and not just working week days.
I know you can select a selection of dates and fill as week days however all of the dates on my primary sheet either have the project start date minus lead time formula or the vlook up formula in it. Filling week days overwrites any of this.
I have a column of dates in Col B that I need to update every day. I want a macro that will automatically fill down the date in the column, but it has to be a working day. So far I have this:
Code:
Range("B3").End(xlDown).Select
Selection.AutoFill Destination:=Range("B3:B" & lastrow), Type:=xlFillWeekdays
However this doesn't work. Obviously the range will need to be dynamic (i.e. it will change from day to day as new dates are added).
I need a formula which counts back a set number of weekdays from a given date. Eg. I am supplied with a project end date and need to schedule 25 week days prior.
I've searched a can only find formulas for the number of weekdays between 2 dates.
I have the following formula....
=IF(B6<1,"",IF(I6<1,"PENDING",IF(J6>0,J6-I6,"PENDING")))
Where B6 = Job Number
Where I6 = Date Received
Where J6 = Date Completed
This calculates calendar days but now I need it to calculate only business days.
Not sure where within this equation I should insert the NETWORKDAYS function
I have a worksheet that displays all the current months weekdays from a starting date in cell b2 using the weekday formula it works accross a row checking next day is a weekday and adding 1 but since some months have more weekdays than others the few cells that are sometimes not needed are then filled with a weekday from the next month how can I stop this? and only have the current months weekdays
View 3 Replies View RelatedI am currently assigning dates to a list manually by giving 5 dates on Mondays to Fridays. I currenly need to do this for around 500 rows and as you can imagine this takes some time.
Is there a way to have a formula that would assign for instance 5 dates on each weekday, purley by dragging the formula down?
For example....
ID
Date
a
07/30/12
b
07/30/12
[Code] ......
I want to get the list of days which are only weekdays ( excluding sat, sun but includes holidays during weekdays) I have tried WORKDAY function but it is not exactly i want coz it excludes holidays during weekdays as well.
View 5 Replies View RelatedI am using office 2003 and I need to create the conditional (just like in the attached image), but I need that the days -1 (day) -2(days) or -3 (days) that are in the end of each formula, I need them to be weekdays (i don't want the to count weekends.
View 3 Replies View RelatedI would like to be able to auto fill weekdays (skipping Saturday & Sunday) in a month. For example, my spreadsheet would look like this if cell A1 contains12/01/09:
DATE WORKDAYS TASK
1-Dec 1 AP CLOSE - CAPITAL & POS ITEMS
1-Dec 1 POS MONTH END
1-Dec 1 HEALTH INSURANCE
2-Dec 2 DEPR SYSTEM RUN; INCL-SOFTWARE AMORT
2-Dec 2 SPECIAL EQUIPMENT
4-Dec 4 PRELIMINARY ENERGY REPORT
7-Dec 5 AP CLOSE - ALL OTHER ITEMS
The DATE column needs to return the date for WORKDAYS listed...Monday through Friday only. The first workday in Dec 2009 is 12/1, the fifth workday is 12/7, and so on.
i had a problem to fill in weekdays of 2014 excluding friday and saturday as holidays.
View 2 Replies View RelatedI have a sheet which basically does this:
If A9 is blank, return 'A9', if not, return 'blank'. (to identify which cells are populated on another sheet)
It does this from A9 to X9.
Then in A10, if A9 says 'A9', just return 'A'.
So the end result has the entire A10 Row with all the letters that aren't blank. In my sheet, I have the following:
A10 = A
K10 = K
N10 = N
P10 = P
Q10 = Q
What I want is to have cell A11 to return (for example) = A, K, N, P, Q - as they are the only values found.
Is that possible without using a macro?
I need to limit the number of letters that a person can write in a given cell.
View 5 Replies View RelatedHow do I do data validation/cell formatting so that only alphabetic characters can be entered and no numbers?
(We use the cell for First Name, Last Name, etc. and I don't want potential entries like Mr. 56565 55454 because the cells are formatted as text and allow alphabet and numbers.)
I tried =NOT(ISNUMBER(H6)) for Data Validation, but that didn't work.
??
VR/Lost
I have some code that loops through a sheet exporting each row to it's own file. The code below is the part that writes the file. The variable "i" is used to count which row the loop is on. I had planned to use the text in the first cell to identify the files, but the problem is that it contains some characters that cannot be used in Windows file names. So what I had hoped to accomplish was to just use the first 7 letters from each cell in column "a" in the filename. how to modify this to only take the first 7 letters from the cell instead of the entire cell contents? Set a = fs.CreateTextFile(DestFile & Cells(i, 1).Text, True)
View 2 Replies View RelatedI have a file with 3 sheets with Sales data. First sheet is for capturing Sales data, second sheet, with some Weekly Analysis and Monthly Analysis using COUNTIFS, third is charts based on second sheet's formulas.
When I use COUNTIFS function to capture the monthly sales nos, the working days in different months vary from each, obviously. Some months have 20 days, some with 21 days, some with 22/23 days. How to write a correct code for varying working days, because I have to capture only the working days in a month, and in THAT MONTH only, leaving all Sat days and Sun days?
My formula goes like this in cell E3 and I have attached my sample file as well.
=COUNTIFS(Master!$B$2:$B$5000,">="&$D$2-30,Master!$B$2:$B$5000,
"<="&$D$2,Master!$C$2:$C$5000,"="&$D3)
eg: If I want to measure Monthly Sales performance for a salesman, Date of Sales Performance measure will be done on the last WorkingDay/WeekDay of that month, in the month Sep-2013, Performance will be taken on 30-Sep-2013, till the 21 weekdays backwards. i.e. 02-Sep-2013. In the month of Aug or Feb the no of weekdays differs.