Computer Support Forum

Solved: Excel XP Date time difficulty

Question: Solved: Excel XP Date time difficulty

Excel gurus,

Back again with another difficulty...

I have the following imported data in a single cell representing the date time and a variable

06/25/2001 15:27 1.4

With the formula =LEFT(A1,16) and =Right(A1,4) I am able to extract the Date and time from the variable from the cell as follows.
A1 II A2 II A3
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 1.4

What I am having difficulty with is transforming the Date and time (06/25/2001 15:27) to the excel sequential numbers known as serial values (ie 37067.64375). What I want is the following:

A1 II A2 II A3 II A4
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 37067.64 II 1.4

I have tried formatting the data to date or to custom m/d/yy h:mm AM/PM or m/d/yy h:mm without success. Any suggestions?

Relevance 100%
Preferred Solution: Solved: Excel XP Date time difficulty

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 (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: Excel XP Date time difficulty

9 more replies
Relevance 78.72%

I have a cell with a date value of "4:00:00 PM" and I'd like to be able to hit the + key and increment it by 1 hour (so it would become "5:00:00 PM", then "6:00:00 PM", etc.). Is that possible?

P.S. I'm using Excel 2003.

Answer:Solved: Excel: Can I increase date/time with +/- key

9 more replies
Relevance 77.08%

I'm suffering formula confusion...

In a simplified example, say I have an Excel file with data in columns A, B, C and D - Item, Position, Start and End.
These might be something like:

ABC__Pos1__20/09/2014 15:00__21/09/2014 09:00
DEF__Pos2__20/09/2014 23:00__21/09/2014 11:00
GHI__Pos1__21/09/2014 13:00__22/09/2014 06:00
JKL__Pos1__23/09/2014 17:00__24/09/2014 10:00
JKL__Pos2__23/09/2014 17:00__24/09/2014 10:00
MNO__Pos3__24/09/2014 09:00__24/09/2014 20:00
PQR__Pos1__24/09/2014 19:00__25/09/2014 05:00
Click to expand...
1) The first result I need is, if any of these time/dates overlap for different Items, to say "YES" in column E and if not, to say "NO" (or TRUE / FALSE or whatever and I'll do a find-replace later).
So I'd end up with:

ABC__Pos1__20/09/2014 15:00__21/09/2014 09:00__YES
DEF__Pos2__20/09/2014 23:00__21/09/2014 11:00__YES
GHI__Pos1__21/09/2014 13:00__22/09/2014 06:00__NO
JKL__Pos1__23/09/2014 17:00__24/09/2014 10:00__NO
JKL__Pos2__23/09/2014 17:00__24/09/2014 10:00__NO
JKL__Pos3__23/09/2014 17:00__24/09/2014 10:00__NO
MNO__Pos3__24/09/2014 09:00__24/09/2014 20:00__YES
PQR__Pos1__24/09/2014 19:00__25/09/2014 05:00__YES
PQR__Pos2__24/09/2014 19:00__25/09/2014 05:00__YES
Click to expand...

ABC's date range overlaps the date range of DEF, so both say YES.
GHI doesn't overlap anything, so it says NO.
JKL appears in 3 different positions during its stay, but it doesn't overlap with anything else, so these ... Read more

Answer:Solved: Excel If date/time ranges overlap, with condition

8 more replies
Relevance 77.08%

Hi there,
I came across this thread (now closed)

Adding Date and Time Last Modified to footer of an Excel worksheet.

It adds the date to each worksheet.
Could someone please show me how to modify it to add the date to each worksheet ONLY IF that particular worksheet has changed?

Thank you in advance.

Answer:Solved: Adding Date and Time Last Modified to an Excel worksheet

Hi there,

Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.


3 more replies
Relevance 76.26%

I have the following macro below which works great for when any value is entered in Column C, it time/date stamps the adjacent cell in Column D. However, I need it to do the same thing a few columns over (ie, when any value is entered in Column H, it will time/date stamp the adjacent cell in Column J)

For the life of me, and cannot get it to repeat . Any ideas? I'm a bit new at this, but thanks in advance for the help. Feel free to spell it out - I won't be offended.

Thanks! Here's the current macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C3:C9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


Answer:Solved: Excel Macro - Repeat Time/Date Stamp for Different Column?

6 more replies
Relevance 76.26%

Is there a way to add Date and Time Last Modified to an Excel worksheet footer, so I can can tell from the various "work in progress" hardcopy printouts what is the latest version?


Answer:Solved: Adding Date and Time Last Modified to footer of an Excel worksheet

16 more replies
Relevance 75.44%

Hi everybody

I have got an hourly meteorological data for 10 years. The problem is that some of the data entries are missing. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 22:00
23-April-2006 23:00

I need to insert the rows for missing hours. Rest of the columns in the inserted row are needed to be blank. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 21:00
23-April-2006 22:00
23-April-2006 23:00

Please also note that for some years, the data entries are for half past hours. e.g.

31-Dec-2001 23:30
01-Jan-2002 00:30
01-Jan-2002 01:30
01-Jan-2002 02:30

Muhammad Zeeshan

Answer:Solved: Inserting rows for Missing Date/ Time data (Excel 2007)

16 more replies
Relevance 74.21%

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.


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
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.

Answer:Excel date auto populate date/time

11 more replies
Relevance 74.21%

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!

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

7 more replies
Relevance 71.34%

Hi,I need to calculate the difference in working days / hours between 2 date / time stamps.Some simple example data which spans a weekend.01/07/2016 18:0004/07/2016 09:30(Working hours are 08:30-17:00 Monday - Friday)In this example the first date / time stamp is when an email was recieved (out of hours) and the second is when the email was replied to (1 hour into workiing hours) So the formula should return 00:01:00:00 (dd:hh:mm:ss)Thank you for your help!!

Answer:Calculating working time between 2 date/time stamps in Excel

It's a rather lengthy formula and needs explanation, so see here: you have further questions, please ask.MIKE

14 more replies
Relevance 68.06%

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!

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

16 more replies
Relevance 67.65%

I have some data I downloaded from a recorder in the form of a *.csv file. Basically, the recorder logged the electric load seen at a power plant in 15 minute intervals from last June up until yesterday. The time and date on the recorder were off by 7106382 Seconds (or 82.2 days).

When I open the *.csv file in Excel 2002, the time and date are together in a cell A12 as 2/15/2007 2:45:00 PM. The cell has a custom format of m/d/yyyy h:mm.

I want to add 7106382 seconds to the time in the cell, and have Excel give me the new date and the new time.

Is it possible? How?

Thanks for the help.

Answer:Excel - Date & Time

Welcome to the forum.

You can have the incorrect date added plus 82.24979167 and it should give you the correct date and time.

2 more replies
Relevance 67.24%

Hi, i'm trying to calculate the amount of time between a scheduled date and time and an actual arrival date and time. I have a query that pulls the data and exports it to excel in the following format.

9/8/2004 00:02:33 9/7/2004 23:47:00
9/8/2004 00:06:00 9/3/2004 23:35:00
I need to calculate the amount of time an arrival is late. I'm not sure the best way to do this. Can anyone help???


Answer:date and time interval in excel

14 more replies
Relevance 67.24%

I'm working with a table with the following date format:

2003-04-05 00:00:00.0

I need to perform some date functions (count days between dates and such) but I can't work with the data when the blank time stamp is there - "00:00:00.0". The data comes off a mainframe db so the data can not be transformed pre-excel.

I have taken it out manually for a few cells in order to do some testing and it works perfectly. I tried truncating it out but to no avail.

Please Help. Work Urgent.
Edit : While I'm at it, anyone know QuatrroPro?
This table contain 440 000 rows, which is too big for Excel to handle (65 xxx limit). Ideally, I would like to do this from QuattroPro but I'm having the same problem. So now, I'm doing it bit by bit and pasting it back into my QuattroPro Spreadsheet until all 440 000 rows are done. QuattroPro has a row limit of 1 000 000 FYI.


Answer:Excel Date-Time truncate...Please Help

8 more replies
Relevance 67.24%

can sum1 hemp me to do a formula for automatic computation or prediction of date & time in excel.

example: current date & time 9/13/2015 8:58 + 60 hours =

i hope someone can spend time to check and help m on this.. thank you & best regards.


More replies
Relevance 67.24%

I am using excel 2007. I have a column where date and time are present in the format mm/dd/yy hh:mm:ss AM/PM. I need to compare this with timings and based on that need to output string like "NA", "AP", "EU".Sample date/time format is: 8/6/2010 1:31:52 AMAs per the timing given below the above date/time should output as "AP"Condition 08:01:00 AM - 05:00:00 PM NA 05:01:00 PM - 02:00:00 AM AP 02:01:00 AM - 08:00:00 AM EUAny help should be highly appreciated.

Answer:Compare date and time in excel

First: Your subject line is a bit confusing. It says "Compare date and time" yet your post doesn't deal with the date at all. Is it safe to assume that we can ignore the date since you only seem to be comparing times?Second, there seems to be a problem with your time ranges. Your ranges all end "on the hour" i.e. 0 minutes but they all start at 1 minute after the hour.What should the output be for 05:00:32 PM? or 02:00:45 AM? or 08:00:01 AMShouldn't your ranges all end in :59?OK, assuming my assumptions above are correct, try this setup:
1 8/6/2010 1:31:52 AM AP 12:00:00 AM AP
2 2:01:00 AM EU
3 8:01:00 AM NA
4 5:01:00 PM AP
The formula in B1 to return AP is:=VLOOKUP(MID(A1,FIND(".",A1),20)*1,$C$1:$D$4,2,1)How it works:Excel stores Dates and Times as numbers, with the integer portion representing the Date and the decimal portion as representing the Time.e.g. 8/6/2010 1:31:52 AM is stored as 40396.0637962963The MID and FIND functions extract the decimal portion of the date in A1 as a text string.MID(A1,FIND(".",A1),20) = .0637962963 (as Text)Multiplying the string by 1 (or adding 0, etc.) will turn it back into a number.The VLOOKUP will then lookup that "number" in C1:C4 and return the corresponding value from D1:D4. Remember that the Times in C1:C4 are actually stored as numbers, so that is what VLOOKUP will look ... Read more

6 more replies
Relevance 67.24%

I have a call tracker that I am trying to automate the input of the date and time. My time is not working correctly and my dates are changed for all entries when the tracker is updated then next day.I uploaded it to my dropbox so you can see exactly whats happening.

Answer:Automating date and time in excel help

I can't access the file right now, but I'll toss this out...If you are using Excel functions such as TODAY() or NOW(), you should be aware that those functions are known as "Volatile Functions"From: Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether the precedent data and formulas on which the formula depends have changed, or whether the formula also contains non-volatile functions.In other words, =TODAY() will return the then-current date, based on the OS date, which means it will change every day. = NOW() will update the Time portion every time the sheet does any type of calculation, anywhere on the sheet.The only way that I know of to automate the entry of the Date and Time and have it remain static is to use VBA (a macro) which can write the actual date and time in a cell as opposed to having Excel pull it from the OS via a function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 67.24%

I have two dates/times that I need to calculate total hours elapsed. For instance 02/03/2011 10:18:27 for one time02/05/2011 19:27:34 for the second timeI need the total hours/minutes elapsedHow would I use excel to calculate this time?

Answer:Calculate in excel with date/time

B - A = XNigelMobo: Asus P7P55D LEOS: Microsoft Windows 7 Professional OEMCPU: Core i5 750 @ 2.67 GHzRAM: Corsair Dominator DHX+ DDR3 1600MH 4GBGPU: Sapphire 4870 D

2 more replies
Relevance 67.24%

I may have my stupid head on but:Excel displays negative dates as an apparently infinite row of hash marks.I have a spreadsheet that calculates hours worked by deducting start time from finish time - all formatted as hours and minutes xx:xx.It then subtracts standard hours from hours worked to give extra hours worked - also xx:xx. So far so good, but it all goes to pot when the employee works less than standard hours and all I get is ### ad infinitum.Any suggestions for a simple solution - I can use an IF function but need to explain it to non IT literate users (or I can use an IF and hide the cell, I guess)Thanks

Answer:Excel negative time / date

You can try Tools > Options and change to the 1904 date system which I believe supports negative times.The alternative is to use a formula like=MOD(B1-A1,1)which will return a positive time even if A1 > B1 - you could use an extra cell to indicate whether that condition was true.

2 more replies
Relevance 67.24%

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?

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 66.42%

Hey!I cannot find a solution for this and i know there are some smart guys out there :)What i need to do is combining a date and a time in 1 cell (but not adding them)Example:A1: 01/03/2011A2: 14:00what i would need now is having it one cell which would be:A3: 01/03/2011 14:00Thanks a lot in advance!Manuel

Answer:EXCEL Combine Date and Time in 1cell

Try:=A1+A2Provided A1 is formatted as a Date and A2 is formatted as a Time.MIKE

8 more replies
Relevance 66.42%

My DAQ system outputs the time and date for each data point collected in the first column along with the various signals in other columns, and I have at least 4k data points in each column. Time and data is given as shown below.11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10I am trying to extract the time diference between the two data points by subracting the info in two cells, and obviously Excel does not recognize the info in the cell because of the presence of ":11" after hh:mm:ss info.How would I delete ":11" from the column and convert the given info to a Excel readable time/date format so that info in two cells could be subtracted to get the time difference.Thanks

Answer:Time and Date Format in Excel 2007

Are the Time & Date together in one cell?If the times are in there own cells, then Custom Format as:[h]:mm:ss.00This will allow you to keep the full time.MIKE

7 more replies
Relevance 65.6%

In excel 2010 new sheet, I would like to enter order number on column A1 then column B2 will show time and date automatically then after entered data then A1 and B1 should locked the cell and won't change anymore. Is this possible to do it?Thanks

Answer:Auto create time and date in Excel 2010

If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.If there will already be something in B2, then things get more difficult and I will need more specifics.(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)
Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
'and respective row in Column B is empty
If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
ActiveSheet.Unprotect Password:="zzzzz"
Cells(Target.Row, 2) = Now
ActiveSheet.Protect Password:="zzzzz"
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 65.6%

I have pasted data in to Excel that is in two columns
Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?

Answer:Excel 2007: a question about date and time manipulation

Post a picture of a sample worksheet containing sample data so I can fully understand what you need to do.

I will replicate the picture you post and can probably figure it out.

As I understand it, column A contains only something like 13/6/2007 (June 13, 2007). Column B contains hours I guess, but I need a sample to actually look at and ponder.

You want to calculate the total time elapsed between some time on June 13, 2007 in column A and some later time as shown in column B??

Column A contains no reference to hours? Hours are found only in column B?

More info please.

4 more replies
Relevance 65.6%

I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.

Answer:Separating date and time in Excel Office 2007

Excel has formatting for Date and Time.
For column A use a Date format, and for column B use a Time format.
To copy the value from column A to column B a function in column B like this should work
= IF(A1="","",A1)

5 more replies
Relevance 65.6%

This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

Thanks in advance for your time/help ...

Answer:Excel: Extract hour ONLY from a date/time field

Ok, got it to work ... but not sure if there is a better way.

I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

Is there a quicker way out there?

2 more replies
Relevance 65.6%

I'm trying to find an excel formula that would help in finding overlapping count for sessions that have specified starting and ending times..
note: the times and dates are in the same cell for each session as follows: (a sample file is attached as well, please check if the format is not clear here..)

Name Session Start Date Time Session End Date Time
AA 9/22/2016 8:43 9/22/2016 15:43
AA 9/22/2016 8:51 9/22/2016 15:45
BB 9/22/2016 8:58 9/22/2016 15:51
BB 9/22/2016 9:06 9/22/2016 15:56
CC 9/22/2016 9:25 9/22/2016 15:59
CC 9/22/2016 9:40 9/22/2016 18:02
AA 9/22/2016 10:03 9/22/2016 18:09
BB 9/22/2016 10:03 9/22/2016 18:12
CC 9/22/2016 10:08 9/22/2016 18:15
AA 9/22/2016 10:44 9/22/2016 18:21
CC 9/22/2016 10:44 9/22/2016 18:24
BB 9/22/2016 11:00 9/22/2016 18:26
AA 9/22/2016 11:09 9/22/2016 18:33
CC 9/22/2016 11:16 9/22/2016 18:31

Answer:checking overlapping for sessions with time and date excel

can you also provide the expected result from those dates and times
And what you mean by overlapping

The only one thats starts after and ends before a session is the last sesssion which overlaps with the 2nd to last
11:16 to 18:31 overlaps with 11:09 - 18:33 because it ends before and starts after
so thats 2 sessions
will count the number of sessions where the start time is greater or equal and the end time is less or equal
what is the significance of the Name ?

2 more replies
Relevance 64.78%

I imported data from another program into Excel and it is giving me the date and time in this format:10012015 01:40I need it to look like this:10/01/2015 01:40The data is in Cell R72. When I use the formula below, it comes up as 8/15/03 1:40 instead of 10/01/2015 01:40.=DATE(MID(R72,1,4),MID(R72,5,2),MID(R72,7,2))+TIME(MID(R72,10,2),RIGHT(R72,2),0)Any help would be appreciated!message edited by rajasperson

Answer:Convert text string to Date and Time format in Excel

Try this:=DATE(MID(R72,5,4),LEFT(R72,2),MID(R72,3,2))+TIMEVALUE(RIGHT(R72,5))The syntax for the DATE() Function is: =DATE(Year , Month , Day)The Year starts at the 5th Character and is 4 Characters longFor the Month we get the First Two characters from the LEFT() side of the text string.For the Day we start at the 3rd Character and get 2 characters.MIKE edited by mmcconaghy

3 more replies
Relevance 64.78%

This is very strange. I created a time tracking system for the company I work for. What they do is enter the time they start each task throughout the day and at the end of the week it all gets imported via VBA into an access database for the manager to review time cards. We had a need for people to be able to edit a record after importing so I set it up to do that. Problem is I found out when Excel sends the "start time" over to Access, it is sending over the time, for the year 1899. If I open Excel on my PC, click on Cell A1 and type 8:15 AM and hit enter, it shows in the bar at the top "8:15 AM". If i store that time in a variable in VBA and format it to show the year associated with that time it comes through as "12/30/1899 8:15 AM". This totally screws up my ability to edit records because when I programmed Excel to edit records it is searching based on a date/time to find the unique record because for John Doe and his time card he will have only one job starting at 8:15 AM on 4/9/12. So it looks up the record by the combination of the date and time. Problem is Excel is not storing a time as "today at this time" so lookups are failing because the year is wrong.

How come if I type in 8:15 AM in Excel it is not stored as 4/9/12 8:15 AM? The date is in a different cell on the sheet so if there is a way I can append the date with the time I guess that is what I will have to do. But as of yet I don't know how to do that.

Here is ... Read more

Answer:Excel date/time problem. Wrong year being displayed

9 more replies
Relevance 64.37%

Looking for a utility that will allow me to locate files created/modified/accessed using TIME from/to i want to locate those related to a specific occurance. When using just date, I get hundreds of files so would like to isolate further.

Is anyone aware of any such program? Shareware might be ok depending on cost but free is preferred. THANKS!

Answer:Solved: Freeware to find files by date and TIME? Time is the key!

10 more replies
Relevance 64.37%


I am running XP Home SP3 on an Asus 1005HA netbook

Every time I boot up I get a message to say that either I can press F1 to enter setup to set date/time or press F2 to load default values

If I choose F1 and enter the correct date/time and press F10 to save and exit - XP then starts up normally but the next time I boot up, the same problem occurs.

[If I select F2, then XP loads normally]

What can I do to fix this?

Thanks for any help in advance

Answer:Solved: CMOS Set date/time message every time I boot up

Your motherboard battery is dead. You can get a new one at any drug store for about $5.

3 more replies
Relevance 62.73%

Have set the correct time zone, set the date and time, and within half an hour, the time jumps ahead significantly. Time then continues jumping periodically, and date as well. Any patch available for this?

Answer:Date/Time jumps after correct setting of date/time/timezone?

Welcome to the Seven Forums

Perhaps a change of cmos battery is needed. What is happening is a sign of the battery having packed up.

2 more replies
Relevance 61.09%
Relevance 61.09%
Answer:Notepad F5 gives time then date, need to reverse order, to date then time

I believe the answer is available at

3 more replies
Relevance 59.86%

My PC below does not hold the date/time & have to reset it after every startup.

A bit new for battery change?

2011 Mobo Asus F1A55-M LE, 2 CORE, CPU AMD A4-3300 APU,
RAM 3Gbts, GRAFICS RADEON HD, Bios Am.Megatrnts 0502 8/18/11
win XP home.

Answer:Solved: PC time/date

definitely sounds like mobo battery
some last 6 months, some 6 years, it is just pure luck when they go. I have had cases where I changed the battery & 3 months later, it was flat, other times it lasted 5 or 6 years

3 more replies
Relevance 59.86%

I want to add 3 years and subtract 1 day to a date value (Excel 07)... how might one go about doing this?

Answer:Solved: Add time to date

With a formula such as:


2 more replies
Relevance 59.86%

Hello all,

I was wondering if there is a way to setup the time and date of which the document was printed on a specific doc, I dont want it on all documents just on a particular one. Thank you in advance.


Answer:Solved: Date and Time

11 more replies
Relevance 59.45%

I am trying to create a template to calculate the expected number of days for my shipment to arrive and I am cracking my head trying to put in the day as well i.e. for today [Aug-5-2002 Mon] How do I format it?

Anyone can help me? Thanks.
(btw, is it possible in the first place?)

Answer:[SOLVED] Day & Date in Excel 97 SR2

Right click the cell and select format cells, select custom from the list and put in mmmm-d-yyyy dddd and the resulting date will be in your format.

2 more replies
Relevance 59.04%


I've spent most of my afternoon trying to fix this but I've finally bitten the bullet and asked for help.

I have a list of data with dates and times (more than 5000 overall) where the date/time field is in one of two formats:

A. 02/09/2013 13:00
B. 2/09/2013 1:00:00 PM

In option A I can change the format of the data whereas in option B, regardless of the data format I impose, nothing changes. I need to sort the data by date but all records which are set out like option b refuse to be recognised as dates so aren't sorted.

Is there a way I can manipulate data shown as in option B so it can be recognised as date and time like it is in option A.

I'm not sure if I have been very clear so please let me know what other information you need.


Answer:Solved: Date/Time Formats

6 more replies
Relevance 59.04%

A rather stupid mistake I made was when I changed the year of the computer (by clicking on the bottom right corner time) to 2013 instead of 2012. After restarting my computer, it all browsers began to say that invalid certificate for all SSL sites. Google Chrome wouldn't let me into any subdomains and there was no exception button, so I used firefox. Google Cloud Connect on Microsoft Office won't connect to google either.
I don't want to just know how to add an exception on my computer, that's rather annoying and temporary. It'd be better if the root of this problem were found. I found this page on Microsoft saying that crypt32.dll is misrecognizing, but it doesn't say how to take care of the problem. I just recently installed the latest version of Norton Security suite, like Norton 360 but from Comcast. When I used one of the previous versions, it caused to issues, I think it's the time's fault not the firewalls. After all, I used the new Norton for about a month and the SSL issues only started happening after I changed the time. Disabling Norton Firewall does not help.

Answer:[SOLVED] [WinXP]Time &amp; Date - SSL

Ok, why don't you just change the year back to 2012? You can also right click the clock, choose adjust date and time, and go to Internet Time and synchronize it with Windows time.

7 more replies
Relevance 59.04%

I have a 2002 custom built computer with Microsoft Windows XP, Home Edition. Lately, I can't keep the date and time correct. It goes back several years and the month and date is screwed up. Why can't I keep it on time? Do I have a bug, virus, or something working against me? Any help will be appreciated. lilart

Answer:Solved: Date and Time problem

At 7 yrs old, the very first thing I would do would be change the cmos battery. Note when you do this, bios settings will go to default so make sure you know any custom settings you need before changing the battery.

3 more replies
Relevance 59.04%

My date and time on my computer keep changing. This morning I noticed it was wrong and fixed it and when I came back on this just now the time is on 3:32 am, which it is 3:32pm and also the date changed to September 2nd. Same thing last week. Why would this be happening???

Answer:Solved: Time and Date keep changing

7 more replies
Relevance 59.04%

Help - every time i switch on my PC, I get a screen come up with cursor operated menus (with a cyan and red colour scheme) concerned with CPU speed and other stuff. I select 'save changes and exit' and it lets me through to my desktop. There I find that my date and time have reset themselves to 31st december 2001, around 11pm!
My OS is Windows XP.
Help much appreciated!

Answer:Solved: Date and Time re-setting (without me asking!!)

7 more replies
Relevance 59.04%

I have a timesheet type Excel workbook. The week is official over on Saturday. When I open a blank timesheet, I would like for Saturday's date for each week to be automatically inserted. I also need a way for the date not to be updated, if the workbook needs to be reviewed at a future date.

Any help with this would be greatly appreciated.

Answer:Solved: Excel Enter Date

13 more replies
Relevance 59.04%

I have the following code

Dim EndDate As Date

EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

If ws.Range("K4") <> "" Then
'Do Nothing
[B]ElseIf Date = EndDate Then
ws.Range("K4") = EndDate[/B]
ws.Range("K4") = Date + (7 - Weekday(Date, 1))
End If

Answer:Solved: Excel VBA Enter Date

13 more replies
Relevance 59.04%

does anyone know how i could change a column in a table that currently has dates in the m/d/yyyy format to mmddyyyy

here is an example

4/20/1991 to 04201991

this series of table contains hundreds maybe thousands of dates and I have to have the state report done by the end of the day any ideas would be appreciated

Thank You,

Answer:Solved: EXCEL Date formats

Dump it into Word and Use replace the / with nothing. Replace is gotten to via "CTRL-H"

3 more replies
Relevance 59.04%

Hello, I am having difficulty getting Excel to sort by date with the following dates:

Column A

When I select "sort newest to oldest" it sorts the dates this way:


I have verified that the dates are not formatted as text and have altered the way the date is shown, trying it without the zero and with the zero and nothing changes.


Answer:Solved: Excel Sort by Date

I think I discovered my error, the dates showing 02/20/12 should be 02/20/13!

2 more replies
Relevance 59.04%

Great!! I solve the problem also from DerbyDad03, smart you.After input date 29-Aug-17, it automatically changed into 0-Jan-00, the formula box shown =8/29/2017, but it won't affect the sort function. If try to priced with " ' " instead of "=" , it shown 8/29/2017. Follow your suggestion, "Turning off the Transition option" then, fixed.

More replies
Relevance 59.04%

Hi guys,

Pretty new to Excel really, know the basic stuff but the intermediate stuff is beyond me for now. Hence this post.

What I want to do is this:

Have a field on my spreadsheet that contains a fixed date say 06/06/2008 and this cell would be green or a cell next to it would be green.

Now as the date reaches say 28 days in the future, so 04/07/2008 I want the colour to change to red.

Is this possible and if so, how do I do it???



Answer:Solved: Help. Need help with date triggers in Excel

8 more replies
Relevance 59.04%

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?

[email protected]

Answer:(Solved) Excel Date format


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


3 more replies
Relevance 59.04%

OK...since there have been no responses to me 2 previous posts, I hvae another question:

I currently have a formula (graciously provided by bomb #21) that calculates the number of years between the current year and a predetermined date.

However, it is based on the calendar year and not the current date. For example, between now and July 30, 2000 would be 10 years. However, the calculates still result in 10 years even when the date is changed to Feb 1, 2000 the result is still 10 years because the current calendar year has not changed.

Here is the formula:


I need a formula that uses the current date as the threshold for the calculation. I've tried combinations of the DATE, MONTH, YEAR, DAY, etc, functions but can't get anywhere.

Anyone have any thoughts?




Answer:Solved: Excel Date formulas

there may be a better solution - but on first look, I would test the Month () and if month(now) > month(f6) then subtract one from the year calc - and then also do the same for Day if Month(now) = month(f6) not infront of an excel PC right now - but that was my immediate thought - but I'm sure there's a better way just using dates .

3 more replies
Relevance 59.04%

Good afternoon all,

Long time no seek help!! (cos I learnt so much last time )
I have a worksheet containing a list of stock i.e. Col A = Date; Col B = Product Description; Col C = Amount of stock

How can I (hopefully using a macro) end up with a report containing the information in Col A; B and C but sorted/sub-totalled as follows :-

Stock older than 18 months
Stock held in store for 12 - 18 months
Stock held in store for 6 - 12 months
Stock held in store 0 - 6

Does this make sense - Is this possible?

Thanks if you are reading this !


Answer:Solved: Excel Date range

16 more replies
Relevance 59.04%


I have read through a lot of posts and have not come across one that actually fits my issue. I am trying to come up with a macro or a formula that will do the following:

The date (the word Date is A1) is in colume A and the row is what is changing (ie A2 (27-Oct-08), A3 (28-Oct-08), A4 (29-Oct-08), etc) I don't want the weekends if possible. I have tried a formula but something is wrong with it "=DATE(YEAR(A2),MONTH(A2),DAY(A2)) - IF(AND(MONTH(A2)=2, DAY(A2)=29),1,0)" It makes every day (every row the same value, doesn't increament) The IF statement is suppose to check for leap year. The =Today() changes all the days to be the same, which I don't want. Can anyone modify this so that it works or auto puts the date in the next row?
Each row gets a new date, I don't want to have to hand type in the date everydat if I don't have to.

Thanks in advance.

Answer:Solved: Excel auto date

Hi there, welcome to the board!

If you have "Date" in A1, and your start date entered manually into A2, you can put this in A3 and copy down...

2 more replies
Relevance 59.04%

date/time format using d hh:mm How to suppress 0 days?

Answer:Excel date/time format as days hh:mm and suppress 0 days

As far as I can tell, there is no way to suppress the Zero using that Custom Format.You could probably process the data and remove the Zero but you will need either a formula or VBA.Is this the result of a calculation?MIKE

2 more replies
Relevance 58.63%

I run Windows 7 (64-bit) and miss the date showing as with XP. How does one get the date to show?

I have the "gadget" but that only shows when the desktop is naked. When I fill the screen with a window, you can't see the gadget.

Answer:Solved: Displaying date along with time on Taskbar

13 more replies
Relevance 58.63%

On a Access form, I'd like to have a control that identifies how long the database has been in operation.
I'd like to be able to say that today is so many years, months & days since a specified date. Better yet, would be to include hours and minutes, or hours to 2 dps.

At present I can only count days, using:
=Date()-#13/02/99#+1 & " days"

I know I can use the DateDiff function as an alternative, and then I can specify the interval, e.g. I could specify hours as the interval and it would return the number of hours that have passed since that date.
But is there a way to specify multiple intervals?
I want it to return:

2 years, 1 month & 6 days

but certainly not:

2 years, 25 months & 761 days

which I imagine it would, if I entered all arguments into a single text box, using yyyy,mm,dd.

Someone on the Access Programmers site said that I should use DateDiff and then compute what I want from that result.

Does this mean I need to do separate controls for each interval?
And how would I 'compute' them?

Answer:Solved: Access: Elapsed Time Since Specified Date

16 more replies
Relevance 58.63%

I recently had to update my bios and since then my CMOS won't keep the correct date and time. I've replaced the batteries and tried to set it in both bios and after booting up through the clock on the desk top.

It will keep the time correctly until I power down and have to start all over.

Also, I cannot sync the date and time even after I have put in the correct date and time once I have made it all the way to the desk top. I've tried both the gov and microsoft sites. I just get an error message. But it does reset the next sync time to the next day (as opposed to March 30 2005).

(I am new to this forum so if I need to reenter my computer specs I am happy to do so. Also, I searched the forums for my problem but only found the solution to change the mobo battery, which I have already tried to no avail.)

Oh and I have XP MCE 2005

Any help would be great.

Answer:Solved: CMOS checksum bad date and Time

16 more replies
Relevance 58.63%

I have a 9 year old desktop computer whose motherboard was replaced about 4 years ago with an AMD NF4XK8MC or NF4K8MC motherboard. It has a 64 bit 3000 processor. It is running at 1.81GHz and has 1.25 GB of RAM.

The system is running on Windows XP including service pack 2. It is fitted with a 80 GB hard drive formatted to NTFS.

Everything is running OK except that every time I switch on the computer and it boots up, it seems to forget the date and time. I go onto the control panel and reset the date and time and it managers to remember the date and time until I switch off the computer and reboot it. It is only a minor problem but is annoying tthat I have to keep resetting it.

Can you offer any suggestions?


Answer:Solved: My Computer forgets the date and time

7 more replies
Relevance 58.63%

When I first built this system for some reason I set the clock so that it keeps time in 24 hour format. Though by doing so it would still display what day it was along with the date when I would hold the cursor over top the time display.

But here lately for some apparent reason all it displays is the date, and not what day of the week it is.

I have looked all over the control panel for some way to check and see if some setting has been changed inadvertently. But so far I have been unsuccessful at finding the option of setting the clock display.

Answer:Solved: How to adjust the Date & Time Display?

Control Panel | Regional and Language Options

Long date should show Sunday, February 08, 2009 for today. If not, click the Customize... button, then the Date tab.

If the Local has changed from your original setting, just change it back. Your customizations should have been saved.
Depending on why things got changed, they may have been reset though, so you may need to customize the Date and Time display again.



1 more replies
Relevance 58.63%

About a month ago the computer wouldn't boot up and after three times, the MSI Award bios reset itself to safe defaults and I was able to get into the bios. The time and date numbers were jittering and I was unable to set them. After about 10 minutes they settled down and I could set them and I then had to reenter all the info into my bios for non default settings like memory voltage and timeing, etc.

This happened about a few weeks ago and then this morning once again I got the jitter time and date problem and it cleared up after about 10 minutes. After resetting the bios entries and saving, on reboot I got an error message in the post read outs "CMOS checksum error, defaults loaded. Please reenter cpu setting in bios CMOS setup." I went into the bios and found the memory timeing and voltage had been set back to default and the primary graphics adapter was back on default PCI instead of PCIEx, so I reset everything again and saved. On reboot there was a long wait of several minutes on the post check where it checks for "Jimicron Tech PCIE-to-SATA/IDE/Rad controller" and then it finally booted into Windows and seems OK again.

Any ideas of what might be wrong or what I could check would be appreciated. My system specs are in my profile.


Answer:Solved: Bios Time/Date Jitters

7 more replies
Relevance 58.63%

Whenever i restart my pc i get a message date and time not set, press F2 to run default values and continue ..i have to reset the time and date manually everytime i start the PC. I pulled out the cmos batt and put it back but it didn't help, plzz help.

Answer:Solved: problems with date and time settings

7 more replies
Relevance 58.63%

I have a strange problem with the date time clock on my new Compaq Mini Note 702EA.

The clock shows the correct time in the Sys Tray and in the Bios.

Emails received show the sender sent them one hour later than they were actually sent. Emails I sent show they were sent an hour later than I actually sent them. This is the same for my Outlook Express emails which are on Dial Up with the provider Orange and my AOL provider wireless broadband emails.

In Outlook Express the message printed to say the email has been checked for viruses shows the correct time.

In Outlook Express the times of all the emails stored for a couple of years Inbox, Outbox etc have all had their times changed by one hour.

The Mini Note has XP3 SP3 installed.


Answer:Solved: Date Time Clock problems

Make sure you have the correct time zone. May also be a question of "Daylight saving changes".

3 more replies
Relevance 58.63%

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.

Answer: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 Sub
MsgBox Err.Description
Resume Exit_DateQuery_Click

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

2 more replies
Relevance 58.22%

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?

Answer:Solved: Excel 2002 Date Format

6 more replies
Relevance 58.22%

In EXCEL (Office 2002) Is there a way to have a header that is auto matically updated to show the date the file was last edited(changed) ? . It should do this each time the file is edited(changed). Then when the file (.XLS) is emailed to someone and they print it, The last "edit(changed)" date will show .Currently the local date the file is printed is showing.
Manually editing the header is forgotten at busy times.

Answer:Solved: Show last edit date in Excel ?

7 more replies
Relevance 58.22%

Hi all, I have a crazy problem with date entry in my Excel 2007 & it's driving me nuts!!

I have two dates (departure & arrival) that I copy in from a text file. the values are 15/03/2011 & 08/04/2011. (DD/MM/YYY)

When I copy these into excel the 15/03/2011 is copied in "as is" but the other date copies in as 8/4/2011 and is right aligned. If I right click both cells are formated the same.

However if I view them as long date formats I can see that 8/4/2011 is now displaying as Thurs August 4th while the 15/03/2011 remains the same e.g. 15/03/2011

Why is the date 8/4/2011 changed to August instead of April and what do I need to do to make it stay the same format as the other date 15/03/2011.

I urgently need anyone who can help to reach out to me.


Answer:Solved: STRANGE Excel date issue

You are set for the wrong date format. Your machine is using U.S. style mm/dd/yyyy instead of the other format dd/mm/yyyy. The 15/03/2011 date is actually an invalid date the way it is set.

Start>Settings>Control Panel>Language and Regional Settings

Change the short date format or the Format section (change English to English - Australian or UK or whichever is appropriate.


3 more replies
Relevance 58.22%

In Excel 2007, Is there a way show a date stamp of when data was entered into a cell? So when you hold the cursor over the cell it shows you the date the data was entered?

Answer:Solved: Excel 2007 date stamp

7 more replies
Relevance 58.22%

My Challenge:
I have a table with 34 different date ranges representing individual rounds 1 to 34.

Start End Round
04/12/2009 05/15/2009 1

In another worksheet I have a long "to do list" of different tasks which have start and end dates. I would like to set this sheet up so that in a "Round" column, a formula would calculate which round each task is in, given its end date.

Task Start Date End Date Round
Final Material Review 31-Aug-09 31-Oct-09 0

I believe I may need to use either a VLookup or IF formulas, though from what I understand of IF formulas it would be far to complex with 34 different date ranges. I am not sure how to use a VLookup with date ranges in this senario.

Answer:Solved: VLookup in Excel Using Date Ranges

10 more replies
Relevance 58.22%


for each client I have I am calculating a date that is three years in the future minus one day.
(i.e. 02/01/2011 gives an output of 01/01/2014)

I want the formula to check the client's date of birth in another cell and calculate a different date if the child is under 18. this date should be 21 years from the date of birth minus one day.

so excel is checking the date of birth of my client and performing a different calculation dependant on their age.

Please see attachment for an example.

any help would be greatly appreciated.

Answer:Solved: Conditional Future Date in Excel

If you google for it yolu will find a lot of code to calcuate age.
I added the function AGE() to your file and included it your formual.
The sheet must be macro enabled to run.

2 more replies
Relevance 58.22%

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.

Answer: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.

3 more replies
Relevance 58.22%

I need to use formulas to caluculate a given number of workdays before a given date.

For example:
A project is due on August 31

I need to figure
2 workdays before that date
7 workdays before that date
13 workdays before that date
26 workdays before that date

Can anyone help?


Answer:Solved: Excel 2003, Calculating a date

7 more replies
Relevance 58.22%

Now I need to calculate a date from a given date using 13 workdays prior if the given date is a Monday and 14 workdays prior if the given date is a Wed.

Is that possible?


Answer:Solved: Excel 2003, Calculating a date using ifs

This will give you the dates for those and a blank for any other days:
Assuming your date is in column A
=IF(TEXT(A1,"dddd")="Wednesday", A1-14,IF(TEXT(A1,"dddd")="Monday",A1-13,""))

1 more replies
Relevance 58.22%

I am trting to create a chart using excel. I have inserted dates in column 1 - they are not sequential!. When I put the data into a chart it creates extra dates inbetween those I have actual placed in the cells. Help!

Answer:Solved: excel unwanted date columns

not a great solution - but format the dates as text - it should then only show the dates you have entered - and should be correct on the chart - but will be a number in the cells

not sure this is the correct way to go - but it seems to work

see attached

2 more replies
Relevance 58.22%

I am using Excel 2003 windows XP Pro. I am having difficulty filerting the dates. I want to be able to sort different data using the mm/d/yyyy format. Is there a way to do this? Thanks

More replies
Relevance 58.22%

I'm trying to have a header display the last updated date on a spreadsheet. I know how to display the current date, but that might be different from the last update date, which would basically be the last date the spreadsheet was saved. Is there a way to do this? Thanks

Answer:Solved: excel: displaying last save date

9 more replies
Relevance 58.22%

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?

Answer: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.

3 more replies
Relevance 58.22%

I have a tab delimited .txt file. One of the fields has 5-2056 in it. When I open the file in Excel, it gets changed to May-56. How can I stop this? I can't edit the data, and I can't format the cell (it's not an actual cell/spreadsheet).

Answer:Solved: Excel, stop changing to date

If you want the 5-2056 kept as is, then do the following.
Select all the cells with data in
Click on Data, Text to Columns
Delimited, NEXT
Select the delimiters you want to apply e.g.TABS, NEXT
The 5-2056 should now be shown in its own columnand the heading should say general. If so just click on Finish
It should now be in its own columns and the 5-2056 will appear just as that.

hope that works foryou.

Come back if you need more clarity.

3 more replies
Relevance 58.22%

hi, the goal is to calculate the difference between the 'year' portion of 2 date entries in different date formats; example:
if cell 1 contains:
and cell 2 contains:

what is the formula to get the result?

Answer:Solved: excel - date difference in years

6 more replies
Relevance 58.22%

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates on the attached worksheet.

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.

Answer:Solved: Date Alert Excel 2003

I have attempted conditional formatting and am getting nowhere.Click to expand...

should do it
this will test for 31 days less
in one of the conditional format rules put in 3 rules
=if(S2="Y", true, false) make green

=IF(AND(TODAY()>C2, TODAY()-31<C2), TRUE,FALSE) make amber - within 1 month

=IF((TODAY()-31>C2), TRUE,FALSE) make red

see attached spreadsheet - tests Column C2

3 more replies
Relevance 58.22%

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates .

PQRS1FROMTOMid-Year DueM-Y Complete201-Dec-1130-Nov-1231-May-12Y301-Nov-1131-Oct-1201-May-12Y401-Jul-1130-Jun-1330-Jun-12Y530-Jul-1230-Jun-1313-Jan-13N601-Jun-1231-May-1330-Nov-12N701-Feb-1231-Jan-1301-Aug-12Y

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.

More replies
Relevance 58.22%

I'm using Excel 2000 and XP. I'm doing form letters and trying to merge dates that are formatted "20020721". How can I change the format to 7/21/2002 when merged?

Answer:Solved: Excel 2000 date merge

11 more replies
Relevance 58.22%

I have a macro that I want the user to input a date such as 12/08/08 and then conditional format column O for any cells containing date greater than the user's input date. The only thing it highlights is the header. I am not sure how to get the formula= MyDate to function correctly. Any help would be appreciated.

Sub TestUserInput()
' Macro1 Macro
Dim MyDate As Date
MyDate = Application.InputBox("Enter a Date")
' MyDate = DateValue(MyDate)
MsgBox "The name you entered was " & MyDate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="& MyDate"
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

Answer:Solved: Excel 2007 Date Variable

7 more replies
Relevance 58.22%

Hi I realise that this is a common thread but I couldn't find a way to make it work for me in particular.

I have some cells that i want to add dates to in the future. they will contain a date that i want to add 14 days to, so that i know when to delete something two weeks later.

the formula i have used is =DATE(YEAR(Vicki!C16),MONTH(Vicki!C16),DAY(Vicki!C16)+14)

but because that formula is operating on a cell (in a seperate sheet) without a date in it currently, the cell displays 14/01/1900

I would like the cell to display as empty/clear until there is a date in the cell I want to add the 14 days too.

Hope that make sense
Any help gratefully received

Answer:Solved: Excel displaying a date when i dont want it to

Hi tiggly, welcome to TSG.

You need to add an If statement so that Excel will check whether the cell is blank or not. Try something like this:


Hope that helps.

3 more replies
Relevance 58.22%


I hope somebody can help.

I am attempting to set-up a spreadsheet to workout calculations based on working days (not Saturday & Sunday) only.

I can work it out using Saturday & Sunday, how do I exclude weekends from the calculation?



Answer:Solved: Excel 2010 - date Calculations

use networkdays()


3 more replies
Relevance 58.22%

Tech Support Guy System Info Utility version
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

Answer:Solved: Calculating Date Fields in Excel

7 more replies
Relevance 58.22%


I've searched several Excel sites, and I've found VB close to what I need, but I don't enough to make it work for me.

I have info in rows from A to J going across. Each row has information about a certain account. When anything in that row is modified, I'd like cell K to show the date modifed automatically.

I found a script that will do that but it only does A as the target cell and then puts the date in one cell over from the target. I need the target to be A thru J and the date cell to be K always.

Any ideas?

Here is the code I found on the Internet. Perhaps someone knows how to change it to do what I want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Barrie Davidson
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

Answer:Solved: Excel - Auto date when modified

Hi, this appears to do it.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column > 0 And Target.Column < 11 Then
Target.Offset(0, 11 - Target.Column).Value = Now()
End If
End Sub

3 more replies
Relevance 58.22%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like


exit celll with ctrl, shift, enter

1 more replies
Relevance 58.22%

I am in college , have a professor that thinks I understand excel , and have been sitting here for about 5 hours trying to do this and even the simplest things are not working for me.

Below are the directions given, I have used the AND function and filled it out but it does not give me what I need. I need it to look like this with the word valid beside the date, under validity. But it keeps telling me false or invalid when I fill out the AND function guide.

There is more directions listed for other things that I have to do and Have not been able to get correct either. I did get correct the cells that only had to list dates. But I have not gotten correct much more than that.

I am rather frustrated and really need any help anyone can offer.

User Input: employee data (cell a1)
Field Value Validity (cells a2,b2,c2)
Employee start date: 1/1/2009 valid (cells a3,b3,c3)
Employee end date: 12/31/2009 valid (cells a4,b4,c4)
Annual paid vacation days: 10 valid (cells a5,b5,c5)
Hourly pay: $10.00 valid (cells a6,b6,c6)
Hours per day: 8 valid (cells a7,b7,c7)

Employee start date: The date (B3) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#8220;year end&#8221; date in F4, and
o on, or before, the employee end date in B4

&#8226; Employee end date:
The date (B4) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#822... Read more

Answer:Solved: Excel: valid new employee date using AND

Well a couple of things. We won't do your homework for you. You must do the work. So no solutions will be provided to you. What I will do though, is try to give you some insight here so that you may come up with a solution on your own.

The AND() function is not helpful in validating dates, in fact that is a completely false statement. It is a logical function, which means it will have a boolean return (either TRUE or FALSE). The only difference between that and an OR() function is that all arguments inside the function must return TRUE for the function to return a TRUE result. With the OR() function, any one single item in the function must be TRUE for the function to return TRUE, whether that is all of them or just one, but if none of them are TRUE then the function returns FALSE.

Here are some examples...

=AND(2>1,4>8) would return FALSE
=AND(4>2,4<8) would return TRUE
=OR(1=2,3=4) would return FALSE
=OR(1=2,3=3) would return TRUE

So then the next part would be to ensure that your arguments return a boolean return. If you're looking at dates, this would return a boolean return...


That says today's date is equal to the value of A2. That is TRUE or it is FALSE, dependent on what value you have in A2. Make sense?

Another little tidbit of information when dealing with dates in Excel is that one whole day is expressed as one whole number. So half a day (12 hours) would be expressed as 0.5 in decimal form. Thus, to calculate hours, if... Read more

1 more replies
Relevance 58.22%

Sorry for the newbie question, but here goes:

I am using Excel 2007 and I have a spreadsheet that uses 2 dates -- the buy and sell date. I have a column that calculates how long we held the product. I use an IF statement to put a zero in the column if the product hasn't been sold yet which looks something like this:

IF(ISBLANK(buy_date),0,(sell_date - buy_date))

Now I want to summarize the "time-to-sale". I want to count all "0" sales (unsold), all sales between 1-30 days, 31-60, 61-90, through the whole year. The problem is, I can't use a macro because this spreadsheet needs to be "macro-free".



Answer:Solved: Excel formula -- by date summary

7 more replies
Relevance 58.22%

I'm looking for a formula for excel, but can't seem to fine tune one, or find an existing one.

The spreadsheet is for applications. If initial contact happens on say 7/20/2010 and the applicant has 5 days in which to respond, for example. I want a column that posts the due date for follow up automatically, and then also a column that says either NO or YES in regards to Follow Up Needed that also updates automatically based on Date Of Follow Up having passed.

I hope this makes sense. I'm going to attempt to put an example below.

Application Date Applicant Name Date Of Follow Up Follow Up Needed
7/20/2010 John Smith 7/26/2010 YES
7/25/2010 Jane Smith 7/31/2010 NO

EDIT: Well, it won't maintain the spacing, but that should give you an idea of what I mean.

Answer:Solved: Excel Formula if a date is exceeded

11 more replies
Relevance 58.22%

I use a lot of Excel macros at work, often formatting pre-fab reports for aesthetics, etc.

I'd like to be able to insert the current date as a part of the macros. I'm sure this can be done, but I don't yet know VBA well enough to write it out myself.

Any ideas?

I'm using Windows XP w/ SP2 and Excel 2003

Answer:Solved: Inserting date with Excel macros

See the code below. If you want to also include the time in addition to the date you can use replace the word Date with the word Now
Range("A2").Value = Date
ActiveCell.Value = Date

3 more replies