Function to set Date
I would like to know how to set below equation:-
to set the 1-week (i.e Monday) prior to a specific date: e.g. 2009/11/24, i
would like to have the output = 2009/11/16
e.g. 2009/12/31, output = 2009/12/21
Try this. Cell B16 holds the specific date
Microsoft Excel MVP
"J" <J@discussions.microsoft.com> wrote in message
> I would like to know how to set below eq...How do I sort activities by date in contacts?
I would like to add a date field to the activities in individual contacts and
sort tasks by date. How do I do that?
Right click the column headings of the activities list, choose Field =
Chooser, and drag the desired field to the column headings. =
Unfortunately, there is no way to make the setting apply to multiple =
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
and Microsoft Outlook Programming - Jumpstart for=20
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumps...Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik
When I use the concantenate feature my date looks like this:
I tried to format the call every which way - but I cannot get it t
look the original.
Feeling really silly for even asking.. thanks all for your help
Message posted from http://www.ExcelForum.com
="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm")
Norman Harker MVP (Excel)
"bleu808 >" <<bleu808.189yij...Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days
for a feild with a date in it [Sold Date].
Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31
I did =[Sold date]+30 and it did not work
how about dateadd:
dateadd("d", 30, [Sold Date])
"KAnoe" <KAnoe@discussions.microsoft.com> wrote in message
>I have a feild that I would like to show what the date would be in 30 days
> for a feild with a date in it [Sold Date].
...change date format to excel format
Hi, I have a date in 01.03.2010 format, how can I change it by using vba for
every time I import the data?
You can use Data>Text To Columns and use the last tab to set dates.
I just recorded a macro to do it, and after tidying it left
"Motta" <Motta@discussions.microsoft.com> wrote in message
> Hi, I have a date in 01.03...Format Date
I have the following code in a Text Bax in a Report:
=Trim("Born on" & " " & [Date of birth] & " " & ("in" & " " & [Place of
Birth] & " " & Trim("and baptised this day" & " " & [Date of Baptism])))
What I would like, is to add the format command to convert the dates to dddd
Could anyone tell me the corrrect syntax and where this should be placed?
Many thanks for any assistance.
You need the format function.
=Trim("Born on " & Format(...Conditional Formatting & Dates?
This is probably a really simple question, but I can't get my head around it.
In one Column I have a list of dates, and above it I have a calendar
template. I want to set up conditional formatting so that if a date appears
in the list, the cell of that date on the calendar is highlighted yellow.
I put this in the cell of 7th Jan 05 (not US format) This worked great and
the cell was highlighted. But when I try it with any other date after 9th
Jan 2005 it fails!
=$B$51:$B$105=DATE(2005,2,4) - This formula to highlight on 4th Feb d...Total column if in date range
I have a spreadsheet that has statistics on employee incidents. On
column has date of each incident then columns with dollar amounts tha
correspond to the types of incidents. I need to create a worksheet o
subtotals by calendar quarter by type of incident. I can't seem t
figure out how to sort for a date range and subtotal each type.
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
Assume the dates column is in A2 going down, insert a help column B (call it...How to get only the year in the date format in Access
How to get only the year in the date format
I.e in the table in need to display only year
E.g 2005 - should be display " 05" automatically
Custom format the cell as:
Please keep all correspondence within the NewsGroup, so all may benefit !
"yanu" <email@example.com> wrote in message
> How to get only the year in the date form...Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B),
their License number (from another cell 2C)and the Date that License Expires
(From cell 2D). The expire date in "2D" is either the word "none" or a date
that that persons license needs to be renewed.
Those instructors with "None" come out in the calculated field fine, however
the ones with dates come back as whole numbers, Example 8/6/10 shows 40396.
any help will be appreciated
You need to change the format of that cell or column, highlight the cell or
the column, right click o...How OMPM Scanner (offscan) Filter by Access/Modified Date ?
I have problem to inventory excel files on very big file server, but I
believe there are so many documents we no longer need to maintain.
I want to skip files if the access date or modified date longer than 6
month, but don't see the OMPM providing feature about it.
I currently running OMPM since 2 weeks ago and running out of time for
reporting to my manager.
Please help me, this is my critical assignment.
...Line Chart with dates in 5 day working week only
Trying to format a chart so that only the 5 working days of the week are
displayed on the x axis. The source data only has the five days (e.g.
05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005
etc etc) So I have missed out the weekend dates.
When I create the line chart however, the weekend dates appear automatically
and just show no point on the chart, therefore there is a longer line between
Fridays and Mondays!!
Hope this makes sense.
Does anyone have any ideas on how to change this? I have tried looking at
Tools-options-chart and cannot seem to turn...Dsum Problem with date criteria
Kindly solve my problem.
I have 2 tables stock_received , stock_utilized.
stock_received fields -- date,3pcs,4pcs,boxes.
stock_utilized fields -- date,3pcs,4pcs,boxes.
I have successfully made a query to get the the current stock in hand as
select sum(3pcs)- dsum("3pcs","stock_utilized"),
sum(4pcs)-dsum("4pcs","stock_utilized"),sum(boxes)-dsum("boxes","stock_utilized") from stock_received ;
Here i have a complex problem, I cant make query with date criteria.
for example 01/05/2010 to 30/05/2010
I download a csv report from a web based program. Everything works great
but the date comes through as "Jul 21 2009 1:51pm". I do not need the time
in my report. I have tried reformatting the cells, tried to copy to a new
file with the cells already formatted to "7/21/09", opening the cell format
using the F2 key and deleting the time (time will still not be formatted
correctly). The only way I get get just the time is to retype every cell.
What am I doing wrong?
I am using Excel 2002 SP3
When data is downloaded from the web, a lot of "other&quo...Don't pull New Hire into payroll until Hire Date
Currently new employee's input into Great Plains before their actual start
need to be marked inactive or they pull into payroll.
Suggest GP Payroll ignore new users until Hire date or adjust hire date has
- Recommended by my HR & Accounting users.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and the...Date change #2
I have a cell to list when the next payment is due from a client.
would like the payment dates to automatically update after the origina
payment date is reached. Payments are due every two weeks. Th
'payment due' cell is G17. So if the first payment was due today, the
tomorrow G17 would display the date two weeks from today. Thanks fo
Message posted from http://www.ExcelForum.com
Assuming you have a valid date in the cell use:
You will find some details on Date and Times in Excel on this page:
...how to detect that the end of a range is also end of a paragraph?
how to detect that the end of a range is also end of a paragraph?
There are several ways including:
Test whether the range and the last paragraph in the range have the same end
If oRng.End = oRng.Paragraphs.Last.Range.End Then
'the range end is also a paragraph end.
or see what the last character of the range is e.g.
If oRng.Characters.Last = Chr(13) Then
'the range end is also a paragraph end
If oRng.Characters.Last = vbCr Then
'the range end is also a paragraph end
<>>< ><<> >...difference of dates
How can I check for entries made during the last week, what would I put in
for the query. I do have a field where it stores the date.
Also how would I get a report for all my data broken down into weeks?
Week of 8/1
week of 8/8
On Mon, 13 Aug 2007 05:56:05 -0700, GregB <GregB@discussions.microsoft.com>
>How can I check for entries made during the last week, what would I put in
>for the query. I do have a field where it stores the date.
There are a number of date handling functions built in to Access. Just what do
you mean by "the last ...Named range links to external workbook, help!
I have a problem with making charts linking to external workbooks.
Let's say I wan't to make a couple of charts in workbook
"ChartBook.xls", that uses data from linked workbook "DataBook.xls".
I have for example made a named range called "AHeader" defined like
If I use the named range in a field in ChartBook.xls like "=AHeader"
it's fine and shows the correct text even if "DataBook.xls" is closed.
But if I make a chart in "ChartBook.xls" and insert "...Sumproduct usage?
My brain's on overload at the moment, so I'm turning to you guys for
help. I need help w/ a formula that will count the number of sales
that someone had based on a certain date...I thought I could use
sumproduct, but I'm beginning to wonder if I was wrong about that.
The relavant columns are D & M.
Col D Col M
You had the right function in mind.
Try something like this...
Use cells to hold the criteria:
A1 = some date
B1 = some name
=SUMP...Using sumproduct while skipping cells in the array
I'm trying to use sumproduct in a situation with two arrays of
numbers. It works in the place where the arrays are contiguous,
looking like sumproduct($A7:$A15,C7:C15). However, in one place one of
the arrays is alternating with strings. (This format was decreed from
on high, and I don't want to argue with them about it...) I thought I
should be able to fake it by using sumproduct($A7:$A15>0,C7:C15>0) to
ignore the strings, but Excel refused to buy that. Well, actually
Excel seems to buy it but just returns 0, which is obviously wrong. I
tried a bunch of options trying to...Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has
the dates. In order to create customized chart in a userform, for
different range of data(i.e from column D, G and M...), I'd like to
select a range of rows that are between 2 dates and create the charts
accordingly. Or copy to range to another sheet and then create the
charts. I am not so advanced in VBA and any help would be greatly
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
...Report on 3 Queries by Date
Hi I have 3 queries which I would like to join into one based on the
servicedate/taskdate so that for any given date I can see the totals of each
type of task we log for work. I would have joined these using a simple query
however my issue is that not everyday I log an event in any one table (Each
query based on only one table).
Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which
I am okay to do but have no idea how to set up the...How to auto-enter date when cell is clicked?
My spreadsheet is on a shared server, and has several columns of data
entered by various people. These are columns D through L.
In the far right column, I want them to just be able to click on the
cell and that day's date appears. It needs to be static - not
auto-update every time the file is opened. It's in Column L. The top
cell is in Row 4.
When they enter or change data on a row, the date they did it needs to
appear in column L in that row.
The point is to keep their data entry time to an ABSOLUTE minimum. I
know they can select the cell and type control-;, but I'd like to
e...Calculating age in years and months from Date of Birth
I think these formulas help me a lot, but I need the answer in years an
months, not decimals or days! Can you help?
To calculate the number of years from the date, enter the followin
formula in cell B1: =YEAR(TODAY())-YEAR(A1)
The result (rounded): 23 Years
For a decimal solution, use the following formula
The result: 23.25 Years
To calculate the number of months from the date, enter the followin
formula in cell D1: =DATEDIF(A1,TODAY(),"m")
The result: 279 Month
Message posted from http://www.ExcelForum.com