Combined date time cell to separate date & time components
From file dump have combined date time cells eg 14/04/03 14:20 (value
37725.59722). Want to perform time analysis so need to split to 2 separate
cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a
fnc to do this? (Currently convert cell to value, then fncs trunc & cell
less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively)
See the reply to your post in the microsoft.public.excel newsgroup.
Please don't post the same question to multiple groups. It just wastes
the time of those replying to a question that was...How to select day_of_week and/or date given a period?
I have a table with the following columns:
Both, Date and DayOfWeek can be NULL but only one of them can be non-NULL.
DayOfWeek, if not NULL, can have a value 0 - 6 (0 = Sunday).
When given a period (i.e. date range) I need to select rows that satisfy the
1) both, Date and DayOfWeek are NULL
2) if Date is not NULL then it must fall within the period
3) if DayOfWeek is not NULL then it must be included in the period
Could someone please help me with the query?
T...Date time formats keep reverting back to default
I have a column of dates that I assigned a custom format to.
When the data in this column gets refreshed from a data base query the
format keeps reverting back to the default format.
How can I stop this behavior?
I doubt you can. Sounds like the format is coming over with it. Why don't
you record a macro, and give yourself a toolbar button to run. Start
recording, select the column, change the format, hit Ctrl+Home and stop
http://www.officearticles.com/misc/methods_to_run_a_macro_in_m...Macro pause for input
I would like a macro to insert a footer with some data
already in a worksheet. I can do that, but I want it to
pause and offer choices, e.g. a dropdown box or something,
so that the choice is between two or more cells/named
ranges in the worksheet, or choices such as left or right
footer and Active.Workbook.FullName or "Date" such as date
is "Date: &D"
Suggestions greatly appreciated.
Have you tried using Forms in your macro? Or even using
the msgbox command will probably suffice.
Look msgbox up in the help from your macro
>...How to click on a cell and have that link to a sheet
Could someone help me with the following:
I have a spreadsheet and want to be able to click on a
cell and that would take me (link to) another sheet on
that same workbook.
Thank you in advance!
Try inserting a hyperlink (Ctrl+K) in the cell.
>Could someone help me with the following:
>I have a spreadsheet and want to be able to click on a
>cell and that would take me (link to) another sheet on
>that same workbook.
>Thank you in advance!
...Trim Time off Date
Access 2007 linked to SQL 2008 database
I have a query with a date field that has the time included on it. How do I
trim, truncate, remove the time part of the date in the query field named
I might use an expression like the following:
Or perhaps a bit faster
IIF(CNDate is Not null, DateValue(cnDate),Null)
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
GMC -LSND wrote:
> Access 2007 linked to SQL 2008 database
> I have a query with ...some emails are not seen when connecting to another inbox
I see all my emails, but if our secretary makes a connection to my
email-box than certain emails are not seen by her, although she has
full access to my inbox.
this happens to only a few messages, but till she did not see and
recon. them its a problem. If anyone have an idea please let me know.
Are those emails "private"?
I hope this helps you at least a little bit!
Outlook trainer and author of Productiv_IT with Outlook
"I not only use all the brains I have, but all I can borrow." ...Visible control based on other inputs
I have a form where the user choose shift, Sektor, Cell, Date from 4
The cell combobox is based on a query that gives you the cells that
corisponds to a sektor when the sektor is choosed.
The query looks like this
SELECT TblCelle.CelleNr, TblCelle.CelleTxt, TblCelle.Sektor
ORDER BY TblCelle.CelleNr;
TblCelle has the following fields
CellNo, CellTxt, CellVar, CellCat, Sektor
The CellVar can have two input either L or H
I have a text box and a label that should not be visib...how do I add a 'web address' to a cell in excel with a hyperlink?
how do I add a 'web address' to a cell in excel with a hyperlink? I am a new
student to computers, and part of a test is to insert a hyperlink to a cell
in excel linking to a web address. I have searched computer for any
'Hyperlink' connection words, but I think it not on here, Is that possible,
although I have top of range computer. Anyone with a solution? I be ever so
ctrl+k>in the link to select existing web page>either type in (at the
bottom) or point to the url on your browser>enter
dguillett1@austi...Re: is problem after update just a coincidence
Hi, Since an automatic update on the 13th my vista 64 bit operating system is
not functioning properly! First thing I noticed on the reboot was that there
wasn't any sound coming from my speakers, yet it wasn't in mute mode and
nothing showed up in device manager! Tried to reboot the PC to see if that
helped but it was then that I realised that how slow that system had become,
I have a quad core intel CPU and 8GB of DDR3 so it was very noticable how
slow it had become! Tried a system restore to a previous point when I knew it
was running correctly but it failed to restore...Date Format 03-23-10
A date has been entered in cell E4, 31/03/2010 but has been formatted
so as to display as MAR-10. That's okay, that's what I want.
I now wish to refer to that cell at B12 using the following -
="Closing Balance at "&E4
However, this displays as Closing Balance at 40268
I think I have to format the date reference 40268 within my formula in
B12 but do not have a clue how to do that.
The format I want to display is 31-MAR-2010
Can anybody advise?
Try it like this...
="Closing Balance at "&TEXT(E4,"mmm-yy")
-...Auto Fill down to next used cell
I have some data that looks like this:
Store Account #
I eventually want it to look like this:
Store Account #
I'm hoping to avoid doing it by hand. Is there a macro I can record or
write that will accomplish this? Thanks for the help in advance.
I find doing it by hand much quicker than writing (or finding) the macro to do
I am using Excel 2000
In the language of Excel, dates are actually numeric values based on
the number of days since January 1, 1900. i.e., the date 12/30/2003 is
actually the value 37985, or 37,985 days since January 1, 1900....or at
least that's how I understand it. Because of this nifty bit of info,
you can add and subtract dates. *My question, is there a formula that
says ONLY perform the subraction IF dates are present in both cells.*
I explain below.
Try the following, when you have time. Open a new worksheet.
- in A1 type the following formula - =A3-A2
- In A2 type 12/30/...Updating picklist custom values
I'm trying to update values for the Lead Status field. All indications say it
is complete as the new values are visible in Values tab in the propoerties
page of the Customize Forms. Problem is when I back out and start using the
system the updates are not present.
I've tried re-publishing the lead customization using the Deployment manager
but the values still don't show up.
What needs to be done to make the custom picklist values viewable by users
in the systems once they are complete in the settings?
Alright...figured it out. After publishing the through ...sales line item workflow and process holds and requested ship date
It would be nice if we could set up workflow tracking per line item as well
as assign process holds per line item in Sales Trx Entry. It would also be
nice to have the abilility to fulfill all items on an order through a
specified requested ship date and leave items with future requested ship
dates unfulfilled. Then to be able to transfer and ship items that have been
fulfilled without having to fulfill those with future requested ship dates.
Right now I have to backorder those items that have future requested ship
dates just so I can transfer and fulfill the other items on the order...Extracting date from cell
I have a text in a cell which ends with a date. I would like to extract the
date. I tried the following:
Dim ReportDate As Date
ReportDate = Range(Right("L3",8)) - 1
but got a type mismatch error.
How do I extract the last 8 characters of a cell into a Date variable in
give an example of the format of the date
"Fred Smith" <email@example.com> wrote in message
>I have a text in a cell which ends with a date. I w...Linking Sheets shows 0's for blank cells
I have a workbook on the server, that links to multiple files from
I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?
Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
Thanks in advance!
To ignore the blanks and not return zeros you must manually create a trap in the
fo...Updating physical inventory with Portable scanner
I have MS POS 1.2 and one lane. I recently purchased a Falcon PT40 scanner
for inventory purposes. I want to do a physical inventory of all the items i
have in my store and upload it into MS POS. How can accomplish this? How do i
setup the PT40? And how do i import the data into my database so it will be
Any help on this will be greatly appreciate
Ok, i was able to setup the Flacon PT40 portable scanner and was able to get
the item lookup code and quantity. For example, it looks something like below
in a text file(notepad).
Item look up code Quantity
012345 ...Merge cells #2
I am trying to merge 5 cells on a newly inserted row, but the Merge cells
checkbox is checked and greyed out. Protection is off and the cells are
This is probably because a portion of your selection is already merged. You should be able to click on the check box to merge the entire selection
----- NOYB wrote: ----
I am trying to merge 5 cells on a newly inserted row, but the Merge cell
checkbox is checked and greyed out. Protection is off and the cells ar
Any ...Input music to WMP?
I know you can enter music by indicating what folder to watch, but I want
more control. Amazon Downloader is a free program to do this, but it is done
programmatically. Is there a non-programmatic way to do this?
On Mon, 29 Mar 2010 17:50:28 -0400, "Jim Cassilly"
>I know you can enter music by indicating what folder to watch, but I want
>more control. Amazon Downloader is a free program to do this, but it is done
>programmatically. Is there a non-programmatic way to do this?
Hi, it's not...if a linked spreadshet is open can I update from it?
I have a spreadsheet that is linked to a file on another machine and multiple
users access this file. Normally when I open it will update my spreadsheet
automatically, however, there are times it does not. Is there any way to
FORCE this to update every time?
I want to find out how many days there between two given dates . A1 is for
example 01.01.2006 and A2 is 01.02.2006 and in A3 I want the amount of days
there are between A1 and A2.
How can I do that?
I appreciate any help and wish you all a great day!
Format as number
"Jochen Munzinger" <firstname.lastname@example.org> a �crit dans le
message de news:email@example.com...
> Hello everyone,
> I want to find out how many days there between two given dates . A1 is for
> example 01.01....Joining a 5.5 site from a one domain to another (both win2k)
We have exchange 5.5 sp4 installed on a child mixed mode win2k domain.
It is installed on the DC and thus we've moved exchange 5.5 LDAP to
listen on 3389.
Our requirement is move exchange across to a member server in the
parent native mode win2k domain. Our idea was to use the famour Ed
in conjuction with KB259553 (which doesn't really match our situation
exactly but should logically still apply)
The install does not complete and i get a dialog box saying the
&qu...Dates to quarters
Hi, Ive found on this forum a formula which turns a date (dd,mm,yy
into a quarter,year which is great:
="Q"&INT((MONTH(C2)-1)/3)+1 & " " & YEAR(C2)
However its set up to take Jan Feb and Mar as quarter 1, but ou
financial year starts in April. How do i adjust it to represent this.
The formula looks at a date (dd,mm,yy) in column C, should I change thi
with a seperate formula or adjust the one Im using ? If so in eithe
case can someone show me how as Im usless with date formulas.
I have a spreadsheet set up containing products with a uniq reference code. I need to input sales quantities against these products each week. At present this is done manually. Has anyone any ideas how to auto mate this function. The sales quantities can be downloaded into an excel file
you may have a look at pivot tables if I understood you
>I have a spreadsheet set up containing products with a
uniq reference code. I need to input sales quantities
against these products each week. At present this is done
manually. Has an...