Over 1 million tech questions and answers.

Solved: Excel 2007: conditional format when date displayed as format d

Q: Solved: Excel 2007: conditional format when date displayed as format d

I am setting up a spreadsheet for the year and want to use it year over year. I have the cell set up with the date (1/1/2011) with a format of d so that it displays the date as Sat, 1/2/2011 - Sun, etc. Since I want to use this year after year with minimal work and I want to highlight the cells that are equal to Sat and Sun, how would I set up that formula. I tried the basic =A1="Sat" and without quotes but it just returns #NAME. I know working with dates in formulas is different but can't figure out what I need to use.

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2007: conditional format when date displayed as format d

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel 2007: conditional format when date displayed as format d

Read other 8 answers
RELEVANCY SCORE 131.6

This file is from Excel 2007
This is about Conditional Formatting Help
Test file is attached

My question is how can i make row three work for the rest of the rows?

A3:A10000 is a number from 1-5
B:M is just ramdom information that i will be Pasting in

Explination:

A3 has a number from 1-5. Cell has CF so that if 1-5 is entered that number will be a certain color.
Once number is in A3 the other cells B3:M3 will change that same color.

Now if i put any of the numbers 1-5 the cells B3:M3 will change what ever color number is in A3.

With that being said if i take and highlight B3:M3 and take the little small box down at the bottom right hand of the last cell, click and drag down to duplicate the CF, all the cells now refer to A3.

I would like for Each row from A3:A10000 to be able to put the number 1-5 and all the cells with that row B:M to change to the same color.

I have row 3 set up like i want. For example just change the number in A3 from 1 through 5 and you will see what im talking about.

Now i can go in and change the CF for each row but i dont have that kinda time to go from A3:A10000

If anyone can help, Thanks in advance.
 

A:Solved: Conditional Format, Excel 2007 Help

Read other 6 answers
RELEVANCY SCORE 123.2

I have a user who is trying to update an Excel spreadsheet somebody sent them. In one cell, there is a reference to the date located in another cell. However, the way it is formatted, the date in the cell displays as just a number instead of the date ("September 22, 2010" displays in the 2nd cell as "40443".

In the second cell, there is some text along with the formula referencing the original cell containing the date. In the formula bar, it looks like this: ...Los Angeles, California 90013, on "&AP29&"

Yet in the spreadsheet, it displays like this: ...Los Angeles, California 90013, on 40443

In the original/refenced cell (AP29), the text entered into the cell is 9/22/2010, and is formatted to display as September 22, 2010.

Any idea how to correct the formula to display the date correctly?

Thanks a bunch.
 

A:Solved: Date format - Excel 2007

Hi Traci.

It's because the underlying value of the date is a number, which is how Excel interprets it no matter how it's formatted. To change it to display the date, you have to tell it it's a date within the formula. Try this:

...Los Angeles, California 90013, on "&TEXT(AP29,"m/dd/yy")&"

Without seeing the whole formula in the second cell I'm not quite sure about the positioning of the ampersands and quotes, but play around with it a little. Let me know if it doesn't work.
 

Read other 3 answers
RELEVANCY SCORE 119.2

I have a pivot table in Excel 2007 with grouped data based on the year and the quarter, so the date info looks like this:

Code:
Years Date
2009 Qtr1
Qtr2
Qtr3
Qtr4
2010 Qtr1
Qtr2
Qtr3
Qtr4
2011 Qtr1
(I know it's not really code but I didn't know how else to maintain the columns)
I have a chart based on this data but I need the dates (on the horizontal axis) to appear in a very specific format of Q109, Q209 and so on. Is there a way I can either alter what's in the pivot table to achieve this, could I use this data combined with the TEXT function somewhere else on my worksheet, or is there another workaround?

My source data is by month only and I'm using the pivot table to calculate the quarterly averages for me.
 

A:Solved: Excel 2007 pivot table date format for chart

Read other 8 answers
RELEVANCY SCORE 110.4

Hi

Can someone show me how to covert a date e.g. dd/mm/yyyy so that it reads as mm/yyyy only and so that a pivot table doesn't recognise the orignal long date of dd/mm/yyyy.

Tried using custom to mm/yyyy but pivot table still reads the date as dd/mm/yyyy.

Thanks

NCFC (Newbie)
 

A:Excel 2007 - Change date format for pivot tables

hi NCFC,

Don't know about Excel 2007, but in 2003:

Right Click the Pivot Table,
Field Settings,
Select the field name that holds the dates,
Number,
Choose your format.

lol
Hew
 

Read other 2 answers
RELEVANCY SCORE 109.2

Hopefully this can be done easily.

Column D is my bank balance and I don't want everyone seeing it. Especially my wife who tends to spend more at the store if she sees it while I check it
.
How can I keep only the last 4 digits visible?
549.67 would only show
549.67 until I highlight it with mouse.
My wife only has one allergy. Money !
 

A:Solved: Excel 2013 Help needed with Conditional Format

Read other 11 answers
RELEVANCY SCORE 107.2

I have conditional format in row 5, as follows
Condition 1
=(F5="") and dragged across
Format is for No colour
Condition 2
=ISNUMBER(FIND("G",numForm)) and dragged across
Format is for Light green if condition met

Cell Format is set at General

I have two problems
1. Cell F5 produces 4 decimal places
2. If I change Cell format to Number , with 2 decimal places Conditional format of light green does not occur.

Is there a way to have a number cell format (custom or otherwise)to two decimal places and a conditional format that leaves the light green if a number is in F5. If no number cell to remain clear.

Any suggestions appreciated.
Pedro
 

A:Solved: A Conditional Format and cell format proble,

I think you should verify that the format in the conditional format is a number too
Check if there are no quotes arounf that conditional format conditions, this happens quite often
 

Read other 2 answers
RELEVANCY SCORE 106

I have a spreadsheet that shows all of the upcoming events at my work (I've attached a very basic example of the way the spreadsheet is set out).

When an event passes, I'd like the spreadsheet to automatically change the formatting of the rows that contain events that have already passed.

For example, today being 14.12.09, an event for yesterday (13.12.09) would be highlighted in red to show that the day has passed.

If anyone can provide any detail on how to achieve this, I would greatly appreciate it.
 

A:Solved: Conditional Formatting Based On Date Range In Microsoft Office Excel 2007

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:

=$A5<$D$2

Click Format > Patterns > red colour > OK.

Click OK.
 

Read other 2 answers
RELEVANCY SCORE 102.8

I have an elaborate Excel file (a weight reduction program) that includes a cell that shows the first day of a month (dd-mm-yy). Based upon which month of the year is in that cell, I want to make an IF statement that uses the number of days in that month (28,30 or 31 days). If I copy that cell and format it as a DATE per the mm format, it just prints the first letter of that month. Therefore, if there was a "J" in that second cell, the IF statement would not know if it was Jan (31 days), June (30 days) or July (31 days).

Anyone have any ideas?

Thanks
[email protected]
 

A:(Solved) Excel Date format

Hiya

Moved you to Business Applications, as you may get more help here

eddie
 

Read other 3 answers
RELEVANCY SCORE 101.6

Down Under we show our dates as dd/mm/yy. I have a user who types in 3/4/00 and the system automatically reverses the date to 4/3/00. I have checked the properties on the regional settings, and the date options in Excel are also correct. I have tried, in Tools/Options/Calculations to change the date format to 1904, but the system wont accept it.

Any ideas for resolution appreciated.

Ms. Kumara
 

A:(Solved) Excel date format wrong

In my 4 years in customer services for a well known computer company, I have never seen that before.

Microsoft Excel does not control the dates nor the settings, ie currency etc. Which version are you using?

Try using another Microsoft program like Word to see if you have the same problem, if not, then you would need to speak to Microsoft in New Zealand.

Also, double check to see under Regional Settings in Control Panel, in regional settings tab, make sure the setting is English (New Zealand), and press apply (if it is not greyed out. If it asks you to restart, press no and go to the Currency and Date tabs to make sure that the setings are for New Zealand.

If that fails, then you can *force* the machine to your own custom settings, by changing the values.

If all else fails, then it could be Windows itself.

Please email me to let me knowhow you got on.

James
 

Read other 2 answers
RELEVANCY SCORE 101.6

I'm using ms office 2003 excel. I have a column date that is in a format of 20100410 and I would like to convert it to April 10, 2010. Any ideas?
 

A:Solved: change date format in excel

If the column is actually a date (not text) then highlight the column and go to Format, Cells. Select the Number tab and then click on the Date format. A list of formatting options will appear and you should find what you want.
If it doesn't work, do the same thing and select Number. If you actually have dates typed in, then 20100410 would show as 40278.00 - if it is text, it will show as 20100410.00.
 

Read other 3 answers
RELEVANCY SCORE 101.6

I am trying to enter a date in a column as yymmdd but when I enter 09 as the year is drops the 0. I tried using different formats but that is not working either. For example, I changed the cell format to yymmdd and when I entered 090120 it displays 460926. Can someone tell me what I need to do to get it to display as I need?
Thanks!!
 

A:Solved: Excel 2002 Date Format

Read other 6 answers
RELEVANCY SCORE 100.8

Does anyone know how to change the format of the [date] that is put into a footer?

Thanks!
 

A:Solved: Excel - Changing date format in footer

Hi slgraham22,

the only way is via a macro: http://support.microsoft.com/?kbid=213742

lol
Hew
 

Read other 3 answers
RELEVANCY SCORE 100.4

Hi,
First, I'd like to say thank you for providing this great service.
I have a HP Probook, running XP. I have a project spreadsheet, gantt style, to track ongoing projects. I am tracking the start date, the due date and the completed date. I have a conditional format set to highlight a range of dates, yellow, on a calendar from start date to due date. The formula is: =AND(I$5>=$D6,I$5<=$E6). I would like to have the yellow blocks change to green when the project is completed. The date completed would be in cell F6. I would also like to have the same range turn red if the project runs pass the due date. The due date is cell E6. I5 is the first cell in the calendar row that runs across the spreadsheet. Any suggestions would be appreciated.

Thanks again
Tom
 

A:Excel 2007 Conditional Formating Date Range

Read other 9 answers
RELEVANCY SCORE 100

Maybe this can't be done.

Column D is my bank balance and I don't want everyone seeing it. Especially my wife who tends to spend more at the store if she sees it while I figure how much she can spend.

What I'm trying to do is to keep only the last 4 digits visible. 549.67 would only show 549.67 until I drag the mouse over it with left button down.

My wife only has one allergy. Money

A:Excel 2013 Help needed with a Conditional Format

Is this anywhere close?

font color=Automatic
=If>4 digits(left digits=White)
or smaybe something like
font color=White(last 4 digits=Automatic) ?

Read other 6 answers
RELEVANCY SCORE 99.6

This site looks great...thought I saw question a while back when I googled here... After reading thru many programming questions here, I realize I know lots less than I thought! ha. Think its a simple Excel formatting, but its been a while.

I've downloaded UK bank files to Excel, and need to change all dates to US format (12/6/2012 to 6/12/2012), with their corresponding text & numbers columns, and then sort top to bottom (oldest to newest), rather than how they came in (newest to oldest) to complete 2 yr. accounting summary. Excel 2007 on Windows 7 laptop.

Thanks much, guys! Jillian

I've included a sample of the columns from Excel sheet. As you can see, there are 5 columns, (oops maybe it won't show as cols) so all corresponding info has to move with the date, when I sort earliest to latest, after changing format for the 2 yrs I have to do. The format options given did not work, even tho' it IS indate format, not text, but when importing, it was already imported as US, not UK. Need to change. Not sure how to set that up, and have all info follow on corresponding cols. Thanks.

27/06/2012 20-71-64 5346xxxx -3.7 PAYMENT EAST MIDLANDS TRAI REF 177 1969574810 BCC
26/06/2012 20-71-64 5346xxxx -28.16 PAYMENT W M MORRISON PLC REF 174 7731688810 BCC
26/06/2012 20-71-64 5346xxxx -13.99 PAYMENT SAINSBURY'S S/MKT REF 174 7884931810 BCC
26/06/2012 20-71-64 5346xxxx -10 PAYMENT LUL TICKET OFFICE. REF 177 7365217810 BCC
25/06/2012 20-71-64 5346xxxx -422... Read more

A:Solved: Excel date format from UK to US & accompanying text files

Read other 16 answers
RELEVANCY SCORE 99.6

Hello,

I'm working on an Excel project. I need a Macro to find duplicate values in Column D and when a duplicate is found, format the font color and fill color for that row, column A thru E. Any ideas?

What I currently have finds the duplicates, but only contionally formats items in that column. I don't want it to be conditional, but here is what I'm using:

Private Sub btnDuplicates_Click()
Columns("D:.D").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("E2").Select
End Sub

Any idea how to do this?
 

A:Non-Conditional Format: Duplicates Values in 1 Column Format Fill and Font Entire Row

I got rid of the conditional formatting you recorded, because I thought the best way was to loop through the cells in the column and try to find them in the range above and below, and it found them, to format the range of cells from A:E associated with the cells. The problem with using conditional formatting is that you can't loop through cells containing a certain format, because the cell object is not actually formatted. I hope this helps...
Code:

Sub Test()
Dim cell As Range, strValue As String
For Each cell In Range("D1:D10")
strValue = cell.Value
Select Case cell.Address
Case Is = "$D$1"
If Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Case Else
If Not Range(cell.Offset(-1, 0), cell.End(xlUp)).Find(strValue) Is Nothing Or _
Not Range(cell.Offset(1, 0), cell.End(xlDown)).Find(strValue) Is Nothing Then GoTo Format
Continue:
End Select
Next

Exit Sub
Format:
' you can obviously change this to suite your needs
With Range(cell.Offset(0, -3), cell.Offset(0, 1)).Font
.Bold = True
.Size = 10
.ColorIndex = 16
End With
GoTo Continue

End Sub
[\CODE]

 

Read other 3 answers
RELEVANCY SCORE 98.8

Hello.

Would anyone know how to use conditional format when the same word appears twice in one cell?
In the example below, A1:A4

A1 apple
A2 apple, banana
A3 apple, apple, banana
A4 apple, apple

one cells A3 and A4 should be highlighted using conditional format
 

A:Excel Conditional Format if text appears twice in one cell

will there always be a , between the words
if so try

=AND(A2<>"",NOT(ISNA(MODE(FIND(","&TRIM(MID(SUBSTITUTE(TRIM(A2),",",REPT(" ",100)), (ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,",",""))+1))-1)*100+1, 100))&",", ","&TRIM(A2)&",")))))
 

Read other 2 answers
RELEVANCY SCORE 98.8

I have a list of associates (14) that require taking company regulated courses throughout the year. I first would like the cell to change colors based on the date, i.e.: 1 week before, date it is supposed to complete and 3 days late. I also need to send an email (Lotus Notes) from my excel spread sheet, to the associate on the day it is supposed to have been completed. I aatached the file, thank you for your help.
 

Read other answers
RELEVANCY SCORE 97.6

Hello everyone:

We are trying to build an update query in excel to update one date in b2 to the date that is a h2. However instead of returning a date it returns random numbers.

Any ideas?
 

A:Solved: Building Update SQL Query in Excel Return Wrong Date Format

Read other 6 answers
RELEVANCY SCORE 97.6

Hi, I've got something that keeps going wrong.\
I have a large file which I use at work to register and plan my colleague's tasks and resgiter (outside our regular vacation/sick/abscence, etc.) the different calls they are working on allocating this. But that works, that's not the issue.
I have a sheet named "Medewerkers", yes it's Dutch for Employees of Colleagues.

I extracted and stripped this portion. (June 16: removed this attachment, correct attachment in final post)

On this sheet double clicking in Column A on the name will open a userform to edit this one or if you double click the first empty row below it you get a blank form to enter the data.
Since I regularly ran into problems useing the ....ocx calendar picker I wrote my own date picker and it works. Using son supporting code I found the funtion GetDateInLocalFormat() does it's job.

But now the issue:

The person on the sheet has as starting date 01-03-2012 (in Dutch format this March 1, 2012) and ends August 2012

On my system and at work The date pickers selects it correctly, show is and the userfrom shows it as 01-03-2012 but when I press 'Vastleggen" (Commit) the date is saved as 03-01-2012 which in Dutch January 3, 2012 when I double click again the datpicker shows the default data as January 3, 2012.

The cell's format is dd-mm-yyyy;@ the system's settings are for code 31 which is Dutch and I have pasted screen shost of the settings that show this.

I... Read more

A:Solved: Excel (2010) Date format issue dd-mm-yyyy and I can't find the solution!

I changed my code a littel and instead of passing the date in local format I pass it as a serial date and then there is no doubt and the result is correct. But if somebody can offer a better solution, I'm all eyes and ears, and ... maybe it will help others who struggel with date values and the international settings
 

Read other 2 answers
RELEVANCY SCORE 97.6

How can I change the format in one excel 2007 file so that all the sheets are in landscape format? I would like this to be for only one particular file. Thanks.
 

A:Solved: Format Worksheets in Excel 2007

Read other 16 answers
RELEVANCY SCORE 93.6

How do I format how time is displayed in MS Word when I mailmerge time data from Excel?

I am using Excel 2003 and Word 2003.

In Excel, I've entered "2:45 PM" in a cell.

When I import it into Word using mailmerge, "2:45 PM" is displayed as "2:45:00 PM"

How can I format all time merged data to simply display "2:45 PM" in word without the additional ":00" after it?

Thanks for any insight to this.
 

A:How do I format how time is displayed in MS Word when I mailmerge from Excel?

Read other 7 answers
RELEVANCY SCORE 93.6

What format do I use when I type 010106 and want it to appear as 01/01/06?
Thanks.
 

A:Date Format in Excel

Read other 7 answers
RELEVANCY SCORE 93.6

Can anyone tell me how to change the format of the date in a footer. I would like in to read "Jan-27-04"
 

A:excel date format

If you use the Custom Footer option, you can type in whatever you like. I could not find a way to enter the data so that it updates based on your computer's internal settings,
 

Read other 2 answers
RELEVANCY SCORE 93.6

I was searching for a way to enter dates such that they are interpreted in the US (not UK) date format, i.e. mm/dd/yy not dd/mm/yy. It was unresolved in this closed thread http://forums.techguy.org/business-applications/910502-us-uk-date-format-excel.html
Since I've solved it, I'd like to share the simple solutrion with interested parties:

1. Close all Office applications.
2. Open Control Panel>Clock, Language, and Region>Region and Language>Change the date,time, or number format.
3. Under Formats, use the drop down to select "English (United States)".
4. Then close and open Excel. From this point on entering 7/1/11 will give you 1 July 2011, and not 7 January 2011.

Don't forget change it back afterwards (unless of course you're moving to the US
 

A:US to UK Date Format in Excel

You can also format the cells to your needs

Use the custom format
dd/mm/jjjj;@
mm/dd/jjjj;@

The ;@ forces the date to that format, you enter the date as you normally do so if it's Brittish dd/mm/yyyy and it will change to mm/dd/yyyy if the format is set to mm/dd/jjjj;@

What has to be clear for the use tis that he she has to enter the date as he knows ir should be

If they are used to entering day month year they will have to do that even though the format says different.

because February 1 2011 entered as 1/2/2011 will become 2/1/2011 in mm/dd/jjjj;@ format.

I hope I have been able to explain it,
The advantage is that you don't need to rememebr that you changed you country settings
 

Read other 3 answers
RELEVANCY SCORE 93.6

Hi

I have a list of dates in Excel in US date format and I can't seem to get them to switch into UK date. I've tried the classic re-formatting and putting the data into notepad etc. Sure its pretty easy.

Any ideas?
Thanks
 

A:US to UK Date Format in Excel

Read other 7 answers
RELEVANCY SCORE 93.2

1 December 2010-12-01 - that is, when I press enter, it auto sets the date

Anyone knows how to fix it

A:Word 2007 Date format issues

Not sure what you're saying when you press date and time on the insert menu it doesn't insert the format you choose?

alt, shift, d will give you the 11/30/2010 format

Read other 9 answers
RELEVANCY SCORE 92.4

I am perplexed. I have an Excel spreadsheet that contains 3 columns with dates. However when I see the spreadsheet the values do not appear as dates but as decimals - for example 39302.4652777778 . However, when I click and look in the formula bar - the date appears formatted correctly - 8/8/2007 11:10:00 AM. I tried to format the columns, pasting values in a new columns and even re-entering a date itself and still get the decimal even though the formula cell indicate that it is indeed a date. Can anyone help me?

Thanks in advance
 

A:Excel date Format problem

Read other 8 answers
RELEVANCY SCORE 92.4

I have not found a solutions which worked, datevalue returned #value etc. The basic problem is if your system date is not according to the date format in your text date column. So if you have set it to ISO, starting with the year, datevalue will not work etc.
This procedure worked fianally:

If the date format is like '12/31/2011 (US Format)
1. Change date format in CtrlPanel Regional & Language Option to the corresponding format, in this case mm/dd/yyyy
2. Select column with dates
3. Format Cells General.
Dates will change to Datevalue 40908 (Nbr of days since 01/01/1900)
4. Format Custom i.e. ISO Format yyyy/mm/dd (which really should be used everywhere)
5. Voila 2011/12/31
6. Change date Format in CtrPanel back to your Format
 

Read other answers
RELEVANCY SCORE 92.4

I'm trying to create an order form that will have some auto fill fields such as date, consecutive order number etc...

The first snag is the the date cell. Ideally I would like it to auto fill this field with the days date upon opening the spreadsheet, I couldn't figure that out so under 'format cell' I designated it as 'date' and chose dd/mm/yy as the format.

The weird thing is that when I enter the numbers for the date in that cell it show up as a completely different date. Example, I click on that cell and type: 041206 for todays date and the cell displays 10/24/12 ?? What the heck is that?

Thanks in advance!

Steve

A:Excel Date Format Is Screwy?

I have had, what I think to be similar problems and when you set the format to something specific (like mm/dd/yy) that is how you have to enter it, including the "/".

If you do it without the slash then excel interprets it differently and changes it. I am not sure why it does this or what it is doing but I do remember that typing the date exactly how the format was set ... solved my problem.

Read other 7 answers
RELEVANCY SCORE 92.4

I can't find how to change the date in my spread sheet to the following format 00/00/1999. It did work and now it is displaying what I think is the Juilian date. It is showing the correct format in the "edit" box, but in the spreadsheet and when I print it, it is just a number. (36983)

Please help.
 

A:Excel: Incorrect Date Format

Read other 11 answers
RELEVANCY SCORE 92.4

Hi everyone,
I have a spreadsheet acting up.. when I try to put 7/17 for month/date and the cell is formatted to date it gives me 1/0 i think... no matter what date format I put, it gives me something else.
Anyone what any idea what is happening.

thx
AMD2800
 

A:Excel date format is not workinig

Read other 7 answers
RELEVANCY SCORE 92.4

I need to have a column of continuous dates like this
Thur 1/01 or if necessary Thursday 1/01. I can find other formats for date available but not this way. I need to keep the column small. I have a formula that will do this
=TEXT("1/01/04", "dddd/mm/dd") but it will not allow me to auto fill series with the day of the week changing. Can anyone Help. Thanks
 

A:excel 2002 date format

Try a custom format, via Format -- Cells -- Number. Category = Custom, Type = ddd mm-dd (type it in the box). Click OK.

HTH,
Andy
 

Read other 2 answers
RELEVANCY SCORE 92.4

I have a spreadsheet of invoices exported from my accounting program. I created a column and copied the invoice date to it then formatted the column with Custom, mmmm-yy. I want to create a pivot table that says for Apr-14 the total for object Code 6630 $$$$. I want to see that in April, 2014 we spent X for Food from Shamrock, Muder, and Sysco. Then what we spent in May, etc. I might not even care about the Vendor but I do want it summarized by month no matter how I do it, and I am trying to isolate our food costs.

It is not combining all of the Apr-14 lines though, but listing them separately.

Thanks
Carla
 

A:Excel Date Format Won't Pivot Right

i would use the group function , and just use the date field - no need to change
 

Read other 1 answers
RELEVANCY SCORE 92.4

I have an existing Excel spreadsheet that contains the date format mm/dd/yyyy. I want to convert it to mm/dd/yy. Excel will not convert the current cells to the new format without re-entering the information. Can anyone help? Thanks in advance.
 

A:Excel 2003 Date format

Read other 12 answers
RELEVANCY SCORE 92.4

I am trying to combine information from a couple cells to create a date header for a column in a worksheet.

I want the cell to read:

Date: Thursday October, 21 2003

I can get the first two parts but the date comes back as a number:

Date: Thursday 37917

Here is the formula:

= "Date: " &'[WKH Daily Roster.xls]Sheet1'!$C$1& '[WKH Daily Roster.xls]Sheet1'!$D$1

The last part is the date "$d1$1"

I know this is probably simple but I searched help in Excel and on Micros.. site with no luck.

Help!!!
 

A:Format date within Excel Formula

Something along these lines?

= "Date: " &[Book1]Sheet1!$A$1&" "&TEXT([Book1]Sheet1!$A$2,"mmmm, dd yyyy")

HTH,
Andy
 

Read other 3 answers
RELEVANCY SCORE 92.4

Hi,
I'm trying to have the format in selected cells change color when a certain cell has today's date in it, and stay changed.
I highlighted the cells that I want to change, then I went to Conditional Formatting and made a new rule
HTML:
=b2=<now()
It works fine without the less than symbol

what am I doing wrong?

Mike
 

A:Solved: Conditional Format

Read other 6 answers
RELEVANCY SCORE 91.6

Hi again all,

Need a little help and this time I think it is possible I just lack the computer know how to pull it off.

I have attached a summary spreadsheet so I can reference as I go but I will give a little back story first. I want to create a spreadsheet that I can view and at a glance see who is traveling on a particular date and when they are due back. The problem is that the personnel will travel several times a year and I don't want to just create a giant spreadsheet with loads of dates.

Cell A3 is just the persons name and serves as no consequence for what I am asking.

In cell B3 and C3 I want to be able to display when the person is NEXT traveling and when that person is NEXT due back. Now this is the tricky part, I want this to automatically change the value of the cells after the date has passed.

In E and F I have put example dates. So lets say the 10th of July passes I would like the Outbound to change to 20th of Aug, and so forth. The same would apply for the inbound.

My thoughts were I could just dump all the dates at the bottom of the spreadsheet off the page break and have the text white so it is not visable and the sheet will be neat and tidy. There will be several people to track and I dont like clutter.

If anyone is particularly clever, could I even host all the dates for cells B3 and C3 on sheet 2 of the spreadsheet??

Once again, any help would be greatly appreciated.

Thanks & Regards
High
 

A:Format a cell in excel depending on date

Read other 8 answers
RELEVANCY SCORE 91.6

This is a continuation from this previous thread I started but is more specific to changing one more thing before I export the worksheet to a tab delimited file.

Need to add one more thing to this script and it has to do with the way the date is formatted in some of the cells. The cell gets exported as to what the cell format is versus what the true cell value is.

So for instance. The actual cell value that is typed into the cell is 12/23/2013 but the client put in a custom format of d-mmm so it displays as 23-Dec and that is how it gets exported to the tab delimited file. I need the cell to export to the tab delimited file as 12/23/2013.

So I tried this code to change the Date Formatting but this did not work.

Code:
If IsDate(rCell.Value) Then
rCell.Value = DateValue(rCell.Value)
End If
Anyone else got any ideas.
 

A:Excel Date Format Change with Vbscript

Read other 9 answers
RELEVANCY SCORE 91.2

OK - Before anyone starts to yell about multiple posts, I have searched and viewed all of the posts prior to mine yet I run into the same issue each time.

Here's what I'm looking to do:

IF a cell in Column BK is blank AND a cell in Column N is greater than 4, highlight red.

The Formula I currently have is:
=($BK2="")*($N2>3)

There are two things that are happening:

1) Cells are being highlighted red even though they have a value in Column BK (formula above conditions BK to be blank).
2) Cells are being highlighted red even though they have a value in Column N less than 4 (formula above conditions N to be greater than 3).

Please let me know if you require any additional information. I thank you in advance for your assistance.
 

A:Solved: Conditional Format with Two Conditions

Hi
Although your formula works I prefer a more straightforward approach. Assuming data is in rows 2 to 20 this would be your conditional statement;

=AND($N2>4,$BK2="")

You don't say which cells you want to shade. If it is only cols N and BK you'd put this in the "Applies to" box;

=$N$2:$N$20,$BK$2:$BK$20

If you want to shade everything from N to BK you'd enter this

=$N$2:$BK$20
The important point here, and probably the reason your condition is not working, is that the Applies to and the conditional statement have to be consistent i.e. if your Condition statement cites Row 2 as above then your Applies to must start in Row 2 as well.
 

Read other 2 answers
RELEVANCY SCORE 91.2

Hi,
I know how I can use Conditional Format to change the color of a number in a cell depending on a date, but how do I keep the new color from changing back with out using something like a macro to ďpaste SpecialĒ the cell before save and close
Mike
 

A:Solved: keep the color after conditional format changes it

Read other 16 answers
RELEVANCY SCORE 91.2

Hi

Iím trying to keep a running total of bill payments.

Iím using conditional format to change the color of the payment to red
on the day itís due and keep it that color till the end of the month
Each month I will start a new sheet

In module 1, I have the following code:

HTML:
Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Application.Volatile
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function

In the total cell I have
HTML:
=colorsum(C1,B5:B30)
Where C1 is the reference color (red)
Since colorsum won't recognize colors that were made with conditional formatting
Is there a different formula I can use that will recognize the change of color based on conditional format?

Iím using

HTML:
=day(today())>=C5
For conditional format where row C is the due date and the conditional format formula is in B:5
In B6 itís
HTML:
=day(today())>=C6
and so on

mike
 

A:Solved: colorsum and conditional format

Read other 6 answers
RELEVANCY SCORE 90.4

i generate an xhtml , with date formats mm/dd/yyyy (eg. <date> 4/22/008</date>) and open it as an excel.
when i send the xhtml to japan,and the japanese open it as excel ,they see the date fields as dd/mm/yyyy .
however , I want everyone to see the report in my mm/dd/yyyy format.

what should i put int the <date> </date> tag in the xhtml so that it will it will open the same for evey location
 

A:problem with date format in excel, when it is open from different locations

Excel has it's own International setting for date formats so it displays as is chosen in the Excel "Setup". I don't know much about xhtml but you could try surrounding the date in text quotes to con Excel in to thinking it is text and therefore leave it's format alone.
 

Read other 2 answers
RELEVANCY SCORE 90.4

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.
 

A:Excel: Format rows based on date and a few other specifics

Read other 7 answers
RELEVANCY SCORE 90.4

Hi all

I created an Excel spread sheet that the user has to input among other things many dates. The spread sheet that I created has many users doing their share of inputting data. The problem is not the formatting of the dates, but how the date is entered into a cell. I have formulas that use the dates to calculate the days between dates and other date type functions. For example, if someone enters Jan. 15, 2006, the date is entered as a text and the formulas return a #Value. I have seen many types of date entries that return a #Value in many cells.

Is there a way that I can warn the user that he or she has entered the date in the wrong format in a MsgBox?? If someone out there has a macro that can accomplish one of the above, that would save me many headaches. The dates are in specific columns (J and K)and are not random throughout the spreadsheet.

Thanks again,

Mario
 

A:Correct Date format in Excel spread sheet

You can use Data > Validation to warn them (although if they copy/paste, that won't retain what you want).

Perhaps the best is to use a UserForm which can control input and formatting. That way they can not change it even after they enter the data.
 

Read other 2 answers