Over 1 million tech questions and answers.

Converting complex date text fields into date or numbers

Q: Converting complex date text fields into date or numbers

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted

RELEVANCY SCORE 200
Preferred Solution: Converting complex date text fields into date or numbers

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: Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?

Read other 3 answers
RELEVANCY SCORE 104

EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)

AND

CLIENT , Non Billable - at column level

Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by... Read more

A:Solved: Pivot multiple sheets for text, date and decimal fields

Hi there, welcome to the board!

I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?
 

Read other 2 answers
RELEVANCY SCORE 102

Hello,
I have a column of dates that I have formatted like this:
\S\Ayymmdd
The date needs to look like this:
SA160327
This is exactly what I need, except I have to insert these in a csv file, and when I try to copy and paste the date, the formatting is gone, and I just get numbers like this:
42456
Does anyone know how I could copy my dates so that they're just text but in that format?
I'm currently retyping all of them, and I have hundreds....
Any help is much appreciated. Thanks in advance.
Fern.
 

A:Solved: Converting custom date format into text

Read other 8 answers
RELEVANCY SCORE 100.8

I am trying to extract a date from a data cell within an Excel 2003 workbook.

The cell contains the data, "October 10, 2011 11:11:11 PM GMT-04:00" but I want only the date information and for it to convert to MM/DD/YYYY format. Basically, taking that cell and turning it into 10/10/2011.

I have about 500 entries with similar data and I am looking for the easiest way to extract the dates from the text. Mind you, the length of the Months (i.e., January vs. September), Date (i.e., 1 vs 11), and even time vary (1:00 vs 11:11).

Any help would be greatly appreciated!

Found the answer I was looking for:

Assuming the first cell is A1,

=TEXT(LEFT(A1,FIND(",",A1)+5), "mm/dd/yyy")

Copy down, then copy the column and paste values.

You can also use

=DATEVALUE(LEFT(A1,FIND(",", A1)+5))

then format the cell as mm/dd/yyy, then copy down.
 

Read other answers
RELEVANCY SCORE 89.6

I need to create a macro which calculates the number of 5 day workweeks between 2 dates. The output for the calculation needs to be displayed in a specific format, think timeline, ie year on top row, month on second row, number of work weeks in bottom row below the month. Is this possible?

It would look something like this...excuse the shotty formatting and made up numbers:

Input in say...cells A1 and A2
start date 7/15/11 (user entered date)
finish date 2/4/12 (user entered date)

Output in rows C, D, & E

|........................2011.................|......2012......|
| Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb |
| 4.4 | 4.2 | 4.4 | 4.2 | 4.0 | 4.4 | 4.4 | 0.6 |

Is this possible?

Thanks in advance.
 

A:Help With Complex Date Calculation

Read other 16 answers
RELEVANCY SCORE 86.8

I have an Access database to manage inspection checklists. I use the question references to sort the checklists so that the questions appear in the same order as the books they come from. I use two fields for this, one for the book, a second for the paragraph. The paragraphs in the book are numbered, with subsecitons and sub-sub sections, ex: 14.6.9.2.1. Of course, Access doesn't sort these as numbers - so any reference that has a 10 in it is sorted before 2. I used the following code to break the references into separate digits: (only the fifth place is shown here, but the rest are similar)

Part V: Val(Mid([Ref2],InStr(InStr(InStr(InStr(1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")+1,InStr(InStr(InStr(InStr(InStr(1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")-InStr(InStr(InStr(InStr(1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")+1,[Ref2],".")-1))

The problem is, if a reference has less than 5 sections (say 14.6), the third through fifth colums read "#Error". This makes it impossible to sort, giving an "Invalid procedure call" error.

Is there a way to either assign a zero to the "#Error" cells, or to make a sort ignore them? I'm not extremely proficient with Access, but am learning as I go.

BTW, the database has over 6,000 questions, so I would like to avoid re-entering them by... Read more

A:Sorting text fields as numbers

Maybe by creating an update query?
Create a query where you do just that, replace error with 0 using the conditional if statement? IIF(not numeric,0,value) ???
 

Read other 3 answers
RELEVANCY SCORE 84

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced
 

A:Solved: Calculating Date Fields in Excel

Read other 7 answers
RELEVANCY SCORE 84

Hello, I use online accounting software (KashFlow) and part of entering invoices involves clicking into a date input field.  It is one of those where a little calendar shows when you click into the field.  You can also manually type a date into this field too. However. On my brand new Yoga 920 using Chrome and Firefox the date input fields are greyed out and a red 'no access' icon shows when you hover over them.  But you can still click it and the calendar pops up as normal.  The only difference is you can NOT type into them anymore. When the source for the page is viewed it looks like the fields have been designated 'readonly'. There is no such problem on any other computer I own, even when using Chrome or Firefox.  The date fields work perfectly well on these other computers - even on a Sony Xperia tablet I own. The only browser that works properly on my Yoga is Edge.  The date fields work as they should.  It's just Chrome (my main browser) and Firefox where they don't work. This seems like a Lenovo-based problem.  A little Google has revealed a couple of similar problems with other Lenovo users. Does anyone have any clue on how to fix this?  This is utterly bizarre behaviour and it's preventing me from working properly. I have attached a photo showing the problem when using Chrome.  These fields should never be greyed out. Thanks,Matt







... Read more

Read other answers
RELEVANCY SCORE 84

Hi people! I'm new to this forum, and have not been looking around much, but will from now on. While i'm a computer technician, Excel was never a strong point - i was always more of a 'hardware' guy. In any event, i have a question that needs some answering. Bear with me if some of my terminology is not %100 correct. I'll try my best.

I have a 3-page spreadsheet. On the 1st page, i have the values I input. Column A(A5 and down) is the date column. B - S are the numerical monetary values I put in, and T & U are the totals. One is a numerical total, the other a monetary total. On the 2nd page, Column A is again dates(B is reserved for manual input), and C & D are the row totals from the 1st page. I manually input data from E - M, and Column N is =SUM(B2:M2). You get the picture.

Here's where it gets tough. The 3rd page is just a break down of the monetray charges for any given day. What i want is for the 3rd page to update the date on the top to the day it is now, and then the monetray totals for that day only, or the day before, whichever I need. I guess What i want is if I change the date on the top of the spreadsheet page, it will update the totals in each of the cells i have underneath. It's not going to be printed, just for viewing on my screen. Right now, I have to input the data for a daily printout manually. If i can change the date at random, and get it to update the totals from the other page, that would be great.

That bei... Read more

A:Can Excel Automatically Update My Date Fields?

Read other 16 answers
RELEVANCY SCORE 83.2

I have been trying to help a friend learn how to do form letters with his Microsoft Works Suite 2004 programs. He has Windows XP SP2. We were able to successfully merge desired fields from a Works database but noticed that the format of date and time field results was not as desired. The date field always merged as "MM/DD/YYYY" in the form letter no matter what format was used for date display in the database program. He wanted to display, for example, "November 12, 2004" instead of "11/12/2004". Likewise, the time field merged as "hh:mm:ss AM/PM" when he wanted, for example, "11:25 PM" to display.
In Works 4.5 (on another computer), his formatting carried over to the form letter from the database. Not so in the 2004 version. Our temporary fix, was to format the date & time fields in the database as "text" instead of "date" or "time" and then enter the exact display style wanted. Then, the merged value looked the same in the letter as it did in the database.
Is there a better solution to this problem?
 

Read other answers
RELEVANCY SCORE 82.4

I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated
 

A:converting numbers to text in Excel

Fatmah,
I could not find a Worksheet function to do this, but depending how many changes there are and how many different numbers need to be changed, you may be able to do this with a VLOOKUP.

In a test I ran I had numbers with values from 1-12. I created a 2 column range elsewhere on the spreadsheet. The 1st column had the 1.2.3 numbers and the 2nd column had their text equivelent. I inserted a new column to the right of where the original numbers were and entered the following to the right of the first value I wanted to change. The vlookup looked like this =VLOOKUP(A4,$F$4:$G$15,2,FALSE) Note: A4 was the cell that had the value I wanted changed. $F$4:$G$15 is the range of the lookup array. 2 is the column with the text value.

Needless to say, this would not be a good idea if every cell had a different number or there were thousands of possible numbers to be converted, but if you had thousands ofmnumbers in a limited range, this might be helpful.
<BLOCKQUOTE><font size="1" face="Verdana, Arial">quote:</font><HR>Originally posted by Fatmah:
I'm trying to convert numbers to text in Excel for e.g. convert "1" to "one", "2" to "two" etc.

Any suggestions would be greatly appreciated<HR></BLOCKQUOTE>

 

Read other 1 answers
RELEVANCY SCORE 82.4

I'm not a Mac user, but a person I am working with is....
We have data exported from various applications into xls or csv files. When I open the files, I get the information needed. However, if the other person (using a mac) opens the file, the number field is changed to a date. The number input format is xxxx-xxxx & displays for her as x/x/xxxx. The date numbers are not at all similar to the orginal numbers (Example 1012-2012 does not display as 10/12/2012 but as 8/1/2514).
She is using excel for Mac 2011
Any suggestions?
 

A:Numbers changing to date in Excel for Mac

Hi, welcome to the forum,
The problem is that the is too much 'intelligence' built in Excel.
Excel makes it's own intertrepation when enetring a number and if 'it thinks that it's a data' it set the format to date format.

When importing a csv file you can also set the the format general but I don't know how the MAc version does this.
You could Google something like "Excel maintain original fort when reading csv file" and see what that brings you.

You have to thank Miscrosoft for this 'built in intelligence'
 

Read other 2 answers
RELEVANCY SCORE 81.6

I have got an HP Laptop from my previous employer. But this has been cleaned before I got it and i have problems to fix an issue With some numbers and time and date. Can you see whats wrong and please help me.

Read other answers
RELEVANCY SCORE 81.6

I have a MS Access database that links to a oracle database. The date fields are in UTC from 1/1/70 12:00 am. I have an excel spreadsheet that shows me the conversion such as

8/12/2004 12:00am with adjustment to local time is 1092283200

What I would like to do is create an input form for a query where the user can just put in the starting date and time and it convert it to the equivalent UTC number. Right now they have to go to excel and get the equivalent starting UTC number for the date they want. The query is set up to ask for the starting UTC number using the criteria field [Enter Start number] since that is how the linked database stores it.

I have tried to search the web for how to do this conversion but only find how to convert UTC to Access Date time. When I run the query, I have no problem getting this conversion with expression field of

ConDate: DateAdd("h",-4,(DateAdd("s",[EVENT_START],"January 01,1970 12:00:00am")))

Any ideas on converting date/time format to UTC would be greatly appreciated.
 

A:Solved: Convert Date/Time to UTC numbers

I have created a form for Start and End Date (used format of General Date and Short Date). I am using a button to run code as follows to see what I am getting for the txtStartDate but it shows up as 12:00am and not the date entered. Therefore the DateDiff function comes out with -25569 instead of 14256:

Public Sub DateQuery_Click()
On Error GoTo Err_DateQuery_Click
Dim txtStartDate As Date

MsgBox txtStartDate
MsgBox DateDiff("d", "1/1/1970", txtStartDate)

Exit_DateQuery_Click:
Exit Sub
Err_DateQuery_Click:
MsgBox Err.Description
Resume Exit_DateQuery_Click

End Sub
I am hoping it is something simple. Can anyone help?
 

Read other 2 answers
RELEVANCY SCORE 81.2

Hi there,
I have an MS Access database (2000, but primarily operating on PC with Access 2010). The database logs wagons coming to and from our site. Mostly, these wagons are in and out in the same day, but it is possible for them to park up overnight, for which we charge a small fee.

The situation has now changed slightly, in that we now accept wagons staying on site for longer periods (usually a weekend), so I've been asked to introduce an Exit Date field as well, and correct the existing calcualtion of time on spent site.

Currently, the following fields appear in the main table (called Stays):

Arrival Date
Arrival Time
Exit Time

There is also a field called Duration, which (for some reason) is autofilled by an Update Query, with the Update To value set to:

IIF([Arrival Time]>[Exit Time],1440+DateDiff("n",[Arrival Time],[Exit Time]),DateDiff("n",[Arrival Time],[Exit Time]))​

This is clearly inaccurate - it assumes that if a wagon's exit time is earlier in the day than its arrival time, then it must have stayed for 1440 minutes + the (negative) difference between the two times, and if the exit time is later or equal to the arrival time, the wagons has been in and out of site in the same day.

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong),... Read more

A:Solved: MS Access modifying a date and time calc in separate fields

Read other 8 answers
RELEVANCY SCORE 80.4

Lost 2 days of data because in the middle of the day my windows date reverts back to Jan 2002. I have changed the date back to the actual date twice already. Any suggestions?
 

A:Windows XP Date Converting To Jan 2002

Read other 8 answers
RELEVANCY SCORE 80.4

Hi,

I need a help in converting current date into long number.I have attached the excel file and coding as follows.

Sub datesexcelvba()
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow

mydate1 = Cells(x, 6).Value
mydate2 = mydate1

Cells(x, 9).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 10).Value = datetoday2

If mydate2 - datetoday2 = 3 Then

Cells(x, 7) = "yes"
Cells(x, 7).Interior.ColorIndex = 3
Cells(x, 7).Font.ColorIndex = 2
Cells(x, 7).Font.Bold = True
Cells(x, 8).Value = mydate2 - datetoday2
End If
Next
End Sub
Thanks,
Chanaka.
 

Read other answers
RELEVANCY SCORE 78

Hi,

Here is my problem. I'm making a countdown timer in Excel. The process is working fine, but my problem is when I call my Sub to set the time at which I want the countdown to end, I receive the following error:
-------------------
Run-time error '6':

Overflow
-------------------

Here is my code:

Dim TimerEnd as Date
Dim WorldPop as Integer 'This value is picked from my Excel worksheet

TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

Note: WorldPop was tested and the value from the cell is really loaded because I was able to modify another cell value by assigning WorldPop value to it.
---------------------------------------------------------------------

Basically, I store in TimerEnd the time at which I want the countdown to reach 0 (obtained by doing TimerEnd - Now() )

The 1st part of the expression:
(1500 - (3 * WorldPop / 8)) determines in how many seconds the timer should end

The 2nd part of the expression:
/ (60 * 60 * 24) divides the 1st expression to obtain the seconds in decimal value which is added to Now() to get the end time.

What I don't understand is that when I type this expression in a cell of my worksheet that have been formated as date:

=NOW()+(1500-(3*850/8))/(60*60*24)
Note: 850 is just a random number which I tested the result. It can be any number ranging from 0 to 2000

It gives me a valid time in the cell.

What am I doing wrong so that in the code of my subroutine it gives me Overflow?

Is there ... Read more

A:Solved: Excel: Overflow error when converting decimal to date

I found the problem.

The following expression was too long:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

I simplified it to:
TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / 86400)

and it resolved my problem.
 

Read other 1 answers
RELEVANCY SCORE 77.6

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

A:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

Read other 16 answers
RELEVANCY SCORE 77.6

Greetings, and thanks in advance for your help..

Awhile back I formatted my Windows 10 PCs to show ddd dd-mmm-yyyy on my taskbar clock. This worked happily on my desktop and laptop for months, maybe even a year +

Now (probably starting in the past couple days, if not today), on one of those two PC's, this format will no longer display. It is Okay on the other PC.
What has changed that the taskbar clock space used to expand to fit the "short date" format, but now it will no longer expand - and instead just refuses to display the date at all. (NOTE: I've verified the issue by testing shorter formats -- short formats, like "ddd" show up correctly. Only if you request a longer format will the date not display - evidently because it is too long for the clock area. )

Additional Notes that may help:

* "Working" pc is running build 10586 PC with too small a clock space that won't expand -- Build 14393

*Other differences: Notification/Action center Icon is left of clock on the working PC - Right of clock on the non-working
Right- Clicking on task bar gives "properties" option on working PC -- But "Settings" option on the non-working

( I don't want to prejudice responses - but clearly the implication to me is that some recent OS update is most likely the culprit.)

Thank You

Read other answers
RELEVANCY SCORE 77.6

Help! I am using a Seagate Central 4TB NAS drive attached to my router via Ethernet. It has been working fine so far for all kinds of file activities. But whenever I modify a file in one of its folders, the creation date for the file also updates and is
changed to the same time as the new modified date.
Maybe relevant details:
I can move/copy folders to the drive and they maintain their created dates.
It happens for both a private user share on the NAS and for the Public share on it.
It happens for at least image files and text files.
I am able to edit files across two computers on the network just fine, the create date stays the same.
This happens from a new Windows 10 machine and Vista machine both.
I notice sometimes the date created seems to stay same until I access the file the next time.
I can't update the NAS firmware because of error.

If it could be a permissions problem, I have been unable to change it from:
Everyone: Special Permissions only
nobody (Unix Group\nobody): Special Permissions only
nogroup (Unix Group\nogroup): Special Permissions only
(Those were there automatically, they are not something I manually put there. I get "Access is denied" whenever try to add permissions or change ownership.)

I have Googled many phrases of this problem far and wide and come up with nothing. I really need to save the original create date because that's how I sort them in programs like Windows Photo Gallery or Picasa. Does anyone know what's goin... Read more

Read other answers
RELEVANCY SCORE 77.6

I got a Refurbished Dell Latitude D600 Laptop from ebay yesterday on Tuesday, September 26th 2017, It had Intel Pentium M (Non-Centrino) and Windows XP Pro SP3, but when i boot it up, it resets to The Default BIOS Date, can someone help me?

A:Dell Latitude D600 Current Date Resets to The Default BIOS Date

The coin cell battery on the motherboard that keeps time while the system is powered off is probably dead.  Not too surprising given that the D600 is about 15 years old at this point.  If you open the system up, it should be easy to find, so just note the type of battery, e.g. CR2032, and then buy that type of battery at any grocery/convenience store.  Should only be about $2-3.

Read other 2 answers
RELEVANCY SCORE 77.6

The title is self-explanatory. So far, I found a software that is able to change this called 'Attribute Changer' but it only changes the date modified in the folder and, when you go into the actual word application > file > info, the date created and modified are not changed. Any help would be greatly appreciated! Thank you!
 

A:Changing 'date created' and 'date modified' in Microsoft Word 2013

Read other 7 answers
RELEVANCY SCORE 77.6

Hi! Does anyone know what is the format of the date in the field "(battery) Manufacture Date" informed by Lenovo Vantage (Hardware Configuration > Energy)? It seems not to follow Windows setting. In my case, it is set to Brazilian Portuguese DD/MM/YYYY, but there is not a leading "0" in the returned value (see attached image). Does it really disregard the Windows setting and presents it in a fixed format (maybe American English)? When was my battery manufactured (Oct. 07 or July 10)?Thanks in advance.  

Read other answers
RELEVANCY SCORE 77.6

Hello.
I'd like to see Long Date and Long Time in my System Tray instead of Short Date and Short Time. 
But due to some restrictions connected with my work I can not modify Short Date & Short Time to look how I want them to look in System Tray.
So basically I need Windows to show what is set as Long Date & Long Time in System Tray instead of showing Short ones.

Is it possible? Maybe some registry tweaks?

A:How to display Long Date in System Tray (without modifying Short Date)?

Hi,
There is no workaround to display the Long Date in taskbar without modifying the Short Date parameter.Ivan-Liu
TechNet Community Support

Read other 8 answers
RELEVANCY SCORE 77.6

hello to everybody

I need batch code to check if date is modified in three files and that date should be compared with current date (%date%).If so,do some job,otherwise quit (exit /b)

I found a bunch of solutions but what troubles me is date format.
On my machine it is dd.mm.yyyy

Thanks in advance.
 

A:Solved: batch to compare file modified date with current date

Read other 10 answers
RELEVANCY SCORE 77.6

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

A:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

Read other 16 answers
RELEVANCY SCORE 77.6

Searching for files modified in a specified date range (of the past 3 days) gets a search result list titled "date modified", and with the column heading "Date Modified" but the date listed against a file modified today is wrong. The date displayed matches the file creation date & is within the specified range ... but how do I know what the Search is doing?

Can't trust it... !

A:File search (by date range) displays incorrect Mod Date in results

That is very strange. Bump for a possible solution, since I can't think of one off the top of my head.

Read other 1 answers
RELEVANCY SCORE 77.2

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

A:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

Read other 1 answers
RELEVANCY SCORE 77.2

Hi all,

I'm new to Windows 7 (Home Premium 32-bit) and I just can't figure out what's going on here! Basically I want my short date format to be dd/mm/yyyy (UK format) e.g. 05/11/2010, but when I key a date into Excel 2002 it is appearing as 05 11 2010. In Control Panel I have the short date format set to ddd dd/MM/yyyy and the example is shown as Fri 05/11/2010, but in Excel all the date formats are shown with space separators, e.g. 14 03 2001! I don't think it's an Excel problem though as in Windows Task Scheduler the dates are shown with spaces there e.g. '19:25 on Mon 01 11 2010'. The date by the clock though is shown correctly with slash separators!

I know in Windows XP you can select the date separator from a drop-down list, but this option seems to be absent in Windows 7. Under HKEY_CURRENT_USER\Control Panel\International in the registry, sDate was set to a space, but changing that to a slash (and rebooting) didn't fix the problem.

Does anyone have any ideas please what's wrong and how to fix it?

Thanks
Steven

A:Date format using space as date separator rather than forward slash!

Hi,

The option you're after is controlled from within Excel. If you right-click the cell(s) in question you can select 'format cells' and under 'Date' you can select the desired format.


OS

Read other 9 answers
RELEVANCY SCORE 77.2

Hi all,

I'm new to Windows 7 (Home Premium 32-bit) and I just can't figure out what's going on here! Basically I want my short date format to be dd/mm/yyyy (UK format) e.g. 05/11/2010, but when I key a date into Excel 2002 it is appearing as 05 11 2010. In Control
Panel I have the short date format set to ddd dd/MM/yyyy and the example is shown as Fri 05/11/2010, but in Excel all the date formats are shown with space separators, e.g. 14 03 2001! I don't think it's an Excel problem though as in Windows Task Scheduler
the dates are shown with spaces there e.g. '19:25 on Mon 01 11 2010'. The date by the clock though is shown correctly with slash separators!

I know in Windows XP you can select the date separator from a drop-down list, but this option seems to be absent in Windows 7. Under HKEY_CURRENT_USER\Control Panel\International in the registry, sDate was set to a space, but changing that to a slash (and rebooting)
didn't fix the problem.

Does anyone have any ideas please what's wrong and how to fix it?

Thanks
Steven

A:Date format using space as date separator rather than forward slash!

Hi Steven,
 
Thanks for the post!
 
When you set the Short date to ddd dd/MM/yyyy, the date by the clock is shown with slash, however, the date in Task Scheduler doesn’t show the slash. This behavior
is by design. When you double click the time and the trigger tab (for example) in task scheduler, the details are shown with slash.
 
When you change the Short date to dd/MM/yy, the date in the Task Scheduler and the clock become the same.
 
For the Excel date problems, I recommend you ask this question in
Office Forum.
 
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and
other communities who read the forums regularly can either share their knowledge or learn from your interaction with us. 

 
Thanks for your understanding and cooperation!
 
Best Regards,
Miya Yao
 This posting is provided "AS IS" with no warranties, and confers no rights. | Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer
your question. This can be beneficial to other community members reading the thread.

Read other 5 answers
RELEVANCY SCORE 76.4

I have a file that lists a Julian date of, for instance, 37573. How do I convert that into something like 11/06/02?

Thanks in advance!
 

A:Julian Date--How to convert to calendar date in Excel

That's not a Julian date, Beth.

That is actually a date value that Excel provides.

That's how many days it's been since 1/1/1900.

To change it to a real date, you should be able to select that cell or entire column, hit Format-Cell, Number tab, and choose Date, and then any one of the date formats.

Julian dates are what numbered day of the year. For instance, January 3, 2002 would be 3.

Learn lots about dates and times in Excel:

http://www.cpearson.com/excel/datetime.htm

Be prepared to be there for a while.

Happy Thanksgiving!
 

Read other 2 answers
RELEVANCY SCORE 76.4

Hello, I have spreadsheet with a number of dates and times ie (11/19/07 8:57 AM) in column A and data going along with each of these dates and times from columns B-CK. I am trying to limit the list to show the information that is for all times after 2:45PM. The problem is I cannot sort by time and the dates attached to all the times span from 2004-2007. Please help!
 

A:Stripping the date portion of date/time in excel

Read other 7 answers
RELEVANCY SCORE 76.4

The AVG on my Dell thinks the Virus database is out of date even though it has the most current update?

How can I change the date back and what might have caused this to happen in the first place. I've heard of some AVG update problem but didn't find much relevant information on it.

Any suggestion or links would be great.

Thanks,
Andre
 

A:AVG Free wrong date set; Update thinks it's out-of-date

Check the date on your computer.. And, maybe uninstall AVG and reinstall it..

http://www.majorgeeks.com/AVG_Free_Edition_d886.html
 

Read other 2 answers
RELEVANCY SCORE 76.4

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!
 

A:Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2
 

Read other 2 answers
RELEVANCY SCORE 76.4

So I have this excel spreadsheet and an example of it basically looks like this:
Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

Is this even possible, either in a pivot table, or even using formulas?
 

A:Excel 2007 - Start Date and End Date question

You should have posted that in "Business Applications" here: http://forums.techguy.org/16-business-applications/
 

Read other 3 answers
RELEVANCY SCORE 76.4

I am using the "microsoft access date/ time picker" control to pick dates. These dates like termination date of a contract will end up in the database. The problem I have is that when I do this,

rst!dateIntoForce = Me!ActiveXCtlDOForce

I end up with this in my database:

1/6/2003 5:48:43 PM

How can I extract only the date part from "1/6/2003 5:48:43 PM" so that only

1/6/2003

goes into the database
Thanks
 

A:Date Part only from MS Access Date picker control

Format the date before it gets entered into the db. Format([Date], "m/dd/yyyy")
 

Read other 1 answers
RELEVANCY SCORE 76.4

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

A:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

Read other 3 answers
RELEVANCY SCORE 76.4

So I have this excel spreadsheet and an example of it basically looks like this:

Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

(Sorry, the text keeps left aligning so person 2 should actually have 25% under Mar, Apr, and May.)

Is this even possible, either in a pivot table, or even using formulas?
 

A:Excel 2007 - Start Date and End date question

Read other 16 answers
RELEVANCY SCORE 76.4

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

A:Solved: Access - Convert Date Time to Date

Read other 10 answers
RELEVANCY SCORE 76.4

Hi there

I know how to do add a criteria to a Query. For example in the QBE pane in design view in the criteria row I enter this statement: Between [Start Date] And [End Date]. When I open up the form that is bound to the query, a message box pops up and I enter the required dates and I get the required data that is between the dates that I specify. However, I'm now finding out that this method is now becoming a nuisance.

Is there a way that I can have 2 unbound controls - one for the start date and the other for the end date in a criteria form. This way I input the dates I want for a particular customer and the form with the required data shows up. For example, I have already developed a criteria form for customers. I have an unbound combo box control that has customer names and when I click on a particular customer name, all the data for that customer is displayed in a form. In the row source of the unbound combo box control there is a SELECT .... FROM ... statement. I also have a button next to the unbound combo box control that opens up the form for that particular customer. Now I want to add two more Unbound controls in the criteria form, one for Start Date and another for End Date.

This way I can select a customer from the customer combo Box, select the start start date from the start date combo box and the end date from the second combo box control and get the desired data for that particular customer between the specified dates. I have attempted to do this bu... Read more

A:Access - Start Date and End Date in a Criteria form

Mario: What you want is called a query by form (QBF). I don't know how to do them myself, offhand, but here's the MS Technet article:
http://support.microsoft.com/?kbid=235359
 

Read other 2 answers
RELEVANCY SCORE 76.4

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

A:Excel date auto populate date/time

Read other 11 answers
RELEVANCY SCORE 76.4

Excel 2013 and Windows 10 Pro

Today is May-14

Date format is set to show: Mar-16
Pressing Ctrl+; shows: 05 14 2016 in the cell
and it converts to: May-16 after I exit that cell.

Any ideas as to how to fix this or why it adds 2 days to the date?

I don't do much on computer now because of health but when I do and there is a problem, I know some of the experts are here.

Thank you in advance, Ron
 

A:Excel Date shortcut posts proper date and then changes

Hey Ron,

This should be a fairly simple one. I'd hazard a guess and say the May-16 is the month and year rather than the month and day.
If you click the format drop down as shown below, and click the last option for more formatting options.

In the box that pops up, navigate to Custom using the left hand panel to navigate.
Here you can make your own (or use a preset if you wish) format to suit your needs.

Here is a quick key to help you get by:
dd = Day in Number format
ddd = Day in Short text
dddd = Day in Long text
mm = Month in Number format
mmm = Month in Short text
mmmm = Month in Long text
yy = year in 2 digits
yyyy = year in 4 digits

You can separate these with pretty much whatever you want.
All of this data is input into the "Type" field as shown in the picture above.

See the picture for an example, Ironically it is May 16th today so it will appear the same as your issue does, but rest assured, as long as you have those D's in there, it will display the day!

Best of luck,
Let us know how you get on,

Xsage
 

Read other 1 answers
RELEVANCY SCORE 75.6

Ok, I need a formula in Excel that converts a date field (i.e., 7/29/04) to the name of the day that represents (i.e., Thursday). I don't care what form the day is, it can be text or date displayed with just the day.
 

A:Need a formula to convert a date to text

Actually you don't need a formula.

Just format the cell as Custom, with a format of dddd.

That will display only the full day (Thursday), without "destroying" the underlying date data.
ddd format displays the shortened version (Thu).
 

Read other 3 answers
RELEVANCY SCORE 75.6

The code below gives me the Date and Time the file
was created. If another file with a newer date
gets copied over it, the date created stays the
same and only the date modified changes.

I need to get the Date & Time Modified. Does anyone
know how?

Thank you.
Sub ShowFileInfo(filespec)
Dim fs, f, s
Error_Flag = 0
On Error GoTo No_File
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = "Created: " & f.datecreated 'modified ' created
FilDate = f.datecreated
'FilDate = f.datemodified
'MsgBox s, vbSystemModal
GoTo Done
No_File:
On Error GoTo 0
Error_Flag = 1
Done:
End Sub
 

A:VB Text File Date Modified?

Something like this

s = "Created: " & f.datecreated
or
s = "Created: " & f.datelastmodified
 

Read other 2 answers