Over 1 million tech questions and answers.

Solved: Excel: Overflow error when converting decimal to date

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


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':


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:

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 a work around to this?

I'm working on Excel 2003 on Windows XP SP2

Thank you

Preferred Solution: Solved: Excel: Overflow error when converting decimal to date

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

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

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

A: Solved: Excel: 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

Once again, another query from the 'Wendal'......

Is there a quick and easy way to convert HH:MM time format to decimal and/or vice versa? This is needed as I run reports from a database in which data is entered as HH:MM (eg. 10.20 instead of 10.3333333.....)
If there is a way of adding up (correctly) differing amounts of time in HH.MM format, then that would be way easier, but currently, I do the following (which took me ages to work out!!)...

To convert Decimal time into HH:MM, I use this formula (remembering to change 'A1' to whichever cell you are pointing to)


To convert HH:MM time into Decimal, I use this formula (remembering to change 'A1' to whichever cell you are pointing to)



Any suggestions (bearing in mind I have no knowledge of VB)?


A:Excel 97: Converting HH:MM time format to Decimal

Format-Cells, Time.

Use the [h.mm]. This adds up hours, even after they are over 24.

Read other 1 answers

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


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

Read other answers


I just wrote an excel vba script to convert long ip addresses to dotted ip addresses. But it doesn't work

The formulas by themselves work great when entered into an excel cell, but when used in in the vba they give an overflow error.

Here's the script:

Sub conv()
For Each cell In Selection
ip1 = (Int(cell / 256 ^ 3))
ip2 = Int((cell Mod (256 ^ 3)) / (256 ^ 2))
dip = ip1 & "." & ip2
cell.Offset(0, 1) = dip
Next cell
End Sub

The 4th line "ip2=int((cell....." is the one giving the overflow error.

Do i need to declare something or use another command.

Could you please help me out, it's very urgent
Thanks in advance,


A:Solved: Excel VBA Overflow Error

Read other 13 answers

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)


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

Maybe another dumb question, but....

I know that Hex(int) will return the hex value of an integer. However I cannot find an equivalent function for returning the integer value of a Hex string. Does such a thing exist, or do I have to write a function to do this?


A:Converting from Hex to decimal in VBA

Here's what MS has to say (apparently that's a no):

TIP : You can determine the decimal equivalent of a hexadecimal or octal number by typing that number in the BorderColor property of a control (first write down the original value of the property so that you can easily restore it). Microsoft Access converts hexadecimal and octal numbers (&H or &O format) entered in the color properties of a control to decimal numbers.

This article comes close as well.

Read other 1 answers

If I have text data in a cell reading 5.7 and I click double-click on the right side of the column heading to auto-adjust the width of the column, it rounds the number to 6 and makes the colum just wide enough for the 6.

If I make it wider it displays 5.7 again, so I have not lost the data.

Is there a way to stop this "rounding" from happening?

Thanks, Peter

A:Solved: Excel: Displaying Decimal Points

Read other 10 answers

I have a problem with Excel in that the decimal place is displaced by a factor of ten when a figure is inserted into a cell, e.g. 25 becomes 2.5. It doesn't matter if the cell is formatted to General, Number or whatever, or what decimal places are specified. However, if I input 25.0 (note the decimal point), then it accepts the figure correctly. This happens on all Excel documents so I guess it's something in the settings. Any help appreciated.

A:Solved: Excel - decimal point displacement

In Excel 2007 you click on the Office button
Then Excel Options...
Then advanced
Then turn off the tick against "Automatically insert a decimal point", then click on OK

That option is used when people have to enter lots of numbers as may be the case with financial accountants. saves them having to type the decimal point.

In previous versions, you will find it under Tools, Options

Read other 3 answers

I'm trying to auto decimal 2 places in a single worksheet of a fairly large workbook. If I use the Excel Options menu, it becomes global to the entire workbook. Anybody got any ideas? I have Excel 2007 and have seen this done but am not sure what version they were using.

A:Solved: Excel auto decimal placement

Read other 16 answers

I'm trying to create a formula that will round the number in a cell up if it is .17 or greater over the whole number.

For example 4.17 would round up to 5 and 4.16 would round down to 4.

A:Solved: Round in excel if decimal is over a particular number

Read other 6 answers

Hey all,

I am struggling with figuring out the source code on how to convert binary to decimal.

I know that you need to take each digit in the binary number and raise it to the base and power of however many digits there are, like so...

1011 = 1x 2^3 + 0x 2^2 + 1x 2^1 + 1x 2^0 ... which would give the decimal number of 1011.

Could someone please help me figure out how to get this to work in C?

Thank you all in advance...

A:C | Help Converting Binary to Decimal

Read other 12 answers

This I think should be a simple one for one you Excel guru's I am using Excel 2003 and have a simple column of figures that I have formatted to show to 2 decimal places. But is there an easy way of hiding the zero's beyond the decimal point without affecting the numbers that do have figures ?

Example 123.25 would be Ok but 123.00 just looks untidy can I hide those two zero's automaticaly ?

Hope this makes sense

A:Solved: Formatting decimal places in Excel 2003

Hi there,

Yes... and no. Yes because you can hide zero decimal places if you want in custom formatting by using the # sign instead of the 0 (that is a zero) symbol. Like this ...


This does present a problem for some people though. These two values...


.. will show like this (exactly)...


See the decimal point? It sticks out like a sore thumb. The # symbol of the custom format will suppress the numerical character, but there is no way to suppress the decimal, not with this type of formatting. The only way to fix this is to use VBA code to automatically look at the cell, see if it is a whole number, if so, change its format, and if not change the format to your custom format (or whatever you wanted). If you would like this code, we would need to know exactly the range of cells you want this to happen with and when you would like it to happen.


Read other 2 answers

Hi Guys,

I have this code:

Sub copyonWAITING()
Dim I As Integer, lstrow As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("All Flights")
Set ws2 = Sheets("Waiting Confirmation")

For I = 1 To ws1.Cells(65536, "B").End(xlUp).Row
If ws1.Range("AG" & I) = "Waiting" Then
lstrow = ws2.Cells(65536, "B").End(xlUp).Row + 1
ws2.Range(lstrow & ":" & lstrow) = ws1.Range(I & ":" & I).Value

End If
MsgBox ("Update Next Sheet!")
End Sub

Which worked fine until I got to Row I, now it comes up with an over flow error.

A little stuck right now. Thanks for any help.

A:Overflow Error in VBA Excel Code

Integer variables have a maximum limit at 32767. Row index can go above this limit, e.g. cell A32768, which you can't refer to with integer rowindex. Try using Long variables:

Dim I As Long, lstrow As Long

Read other 2 answers

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

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


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

I have a column of dates that I have formatted like this:
The date needs to look like this:
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:
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.

A:Solved: Converting custom date format into text

Read other 8 answers

Hello E.One
in Excel , as I know , if you choose to convert a decimal Number to be one digit only (Integer Number) then the program will follow the math rules and approximate it . that means , if the fraction part is less than five the result of approximation will be the integer part itself and if the fraction part bigger than 5 , then the integer part will be incremented by one .

Now I want a formula that make the approximation always to the higher side (as it is bigger than 5 , whatever the fraction value is) .

is it possible to do so , using a formula ( no Macros no VBA ). if it isnot , can any one recommend a solution....

A:a decimal Number in Excel

The Roundup function should be what you are looking for?

Read other 2 answers


In Microsoft Excel 2003, I need to know how to round a number to two decimal places and add a zero at the end. So if the number is 23.4567, I need it to appear as 23.460.

Thank you

A:Excel decimal places

Read other 13 answers

is there a way i can have an "if statement" that looks to see if a value is x.3? I have a sheet that works out rotas for people and i need to be able to put an if statement in that looks to see if it says 19.30 for example?

hope this makes sense

thank you in advance

A:Excel: If Decimal place

theres a few ways to do that

assume the value is in cell E18


that will return the decimal and then use the

but when i use in an IF statement and test - for some strange reason it works upto 8.3 and then stops

i need to look into some more

if i use any of those functions when I put in anything above 7.3
i get a 1 on the 14th decimal place
so for the IF statement to work - i have had to use round to three decimal place to get rid of the place

how many decimal places are you likely to see in your data

=IF(ROUND(MOD(E18,1),3) = 0.3, "yes", "no")
this seems to work using the
round ( calc , 3) to goto three decimal place

Read other 2 answers


we all know that any decimal .5 or above will be rounded to the next and vice versa.

but i want it to be even .1 will be added to the next instead of below
i.e 2.2 = 3.0
4.1 = 5

is there a way? can the formula IF works or ROUND ?

A:NEAREST DECIMAL for excel xp


Read other 2 answers

I have an excel spread sheet at work that has a column that I have to enter an interest rate on. All of a sudden instead of giving me 4.50% it is giving me 450.00%. I went into format and told it percent with two decimal places but it still gives me the wrong value. What else could I try?

A:getting a 5 with 2 decimal places in excel

never mind I got it. Thanx anyways.

Read other 3 answers

Hi all

I am new in an accountancy job and don't know a huge deal about excel. The last few days I have been having a fairly major problem considering the nature of my job and have no idea what I did to cause it or how to fix it. Whenever I enter a numeric value into a cell in Excel, it is automatically inserting two decimal places in front of whatever I type. So if I type 5, it ends up being 0.05, 55 = 0.55, and 555 = 5.55 etc. This is intensely frustrating, and the only way I have figured out to get around it is to format the cell as text, then type the number, then individually convert each cell to a number with the help function that comes up on each cell because the program recognises it probably should be a number. I can't highlight a cell and format it as a number though, then the decimal place problem happens again. It's taking me a long time to do anything and it happens in new spreadsheets, if I reboot, if I edit a spreadsheet someone else sends me, etc etc. Please please please someone tell me what I need to do to fix it, it's really a huge issue in my hour to hour work!

A:Decimal point problem with excel

On the Main Menu got to Tools>Options>Edit and untick the "Fixed Decimal Places"

Read other 3 answers

I have a web application, and it will create report files with EXCEL 2003 xls format.

My web application is using Apache/2.0.52.

My EXCEL 2003 is Germany Version(Europe use "," as decimal separator).

My created xls files have numeric fields which use COMMA as decimal separtor.

The application provides links to those excel files for users to download.

The screenshot looks like sth as:

The browser is IE8.0.

The issue is:

When I either open the excel files direct through IE8; or right click and "Save as" to my local disk and open it, the decimal separator will change from "COMMA" to "PERIOD".

But on the web server directory ".\htdocs\excelfile.xls", I open this file directly, it is still European format(COMMA as decimal format).

If I use Firfox 3.5, there is no this format automatically switching issue.

So does anybody know why IE8.0 change my Excel decimal separator setting from "COMMA" to "PERIOD"? How should I fix it?

And I am also wonder how IE8.0 is working with EXCEL when I open Excel file through IE8.0?

Any suggestion will be appreicated.

Read other answers

Hello all,

I have a problem that my Lieutenant would like done, but I'm not thinking it is possible. Here it goes:

He has a number, say 120.5, meaning 120 minutes and 30 seconds. He wants to type 117.5 into a cell, and have it displayed like this: 02:00:30. He does NOT want to do any conversions at all, just type number and have it spit out. IF this is possible, I think I'd have to do it in a custom format field somehow. Any ideas??


A:Excel Decimal Time Formatting Problem

This would be one way.
In A1 put 120.5 and format as number to 2 decimal places.

In A2 put this formula =A1/1440 and custom format as h:mm:ss


Read other 1 answers


I'm just wondering how to convert this spread sheet file (.wks) to excel 2007..

i know thats a lot of years in between the two programs but there must be a way to convert it.. or is there?

so i do need to open and re use the spread sheet but i don't know how i got that program in the first place.. so yer.

ive tried to convert it using excel 07' and was a fail. only came up with all differnt characters.

thanks for reading.

A:Solved: converting spreadsheet (.wks) to excel 07

Is this of help ??? http://forums.techguy.org/business-applications/779694-solved-works-vs-excel-formats.html

Read other 3 answers


I have an old Quattro Pro file which I would like to access but do not have Quattro Pro installed any longer. I believe it may be possible to open in Excel. When I opened Excel and click on Open, File Types, I tried Quattro Pro/DOS types but that (and any other trial I've had) returns a warning message ".wb3: file format is not valid". The file had been on a 3.5" floppy and maybe (hopefully not) the floppy is unreadable.

My OS is Windows XP and I have Excel 2003.

Thanks for your time


A:Solved: Converting .wb3 files to Excel

Welcome to the board.

Try this?

Read other 2 answers

I would like to know how to convert a numberic value which includes decimal places (Ex: 3.932) to a time value including minutes (Ex: 3:55). How is this done? I tried using =CONVERT(A1, "hr", "mn"), but it didn't work. Is there anything else I need to try?

A:Excel 2000- Time Converstion to Hours and Minutes from a numeric decimal value

Have you tried right clicking on the cell and changing the format to time value?

Read other 3 answers

On an older computer with a 486 DX2 processor I am trying to install Windows 98. I ran fdisk on the hard drive then formatted the drive. But when I boot up with the floppy disk that came with the Windows 98 CD, I get errors like 'the master boot record will be modified'; answer 'yes' and it continues. When it starts the Windows install from the CD, it gets to the point where a window comes up that says "Setup is preparing the Windows 98 Setup Wizard, which will guide you through the rest of the setup process. Please wait."
Then a black rectangle comes on the screen and the computer freezes.
I tried it again and when it got to this point, where the black rectangle was it says, "Divide by zero or overflow error?
What is this Divide by Zero or overflow error and how do I deal with it?

A:Solved: Divide by zero or overflow error?

i had an old Linux that gave me the same error when switching to windows 98
i know of an error you used to get on linux calculator if you tried to divide somthing by zero
that was exactly the same... i think it has to do with the disk you downloading from
windows 9x altogether has isues with even a slightly dirty disk so try cleaning it the put it in and pray. thats all i can think of

Read other 1 answers


I have tried seaching for this information, but I can't find it anywhere.
What I would like to know is, do function words in Excel convert automatically if sent to someone with Excel in another language.
For example:

I am user English version of Excel. In my spreadsheet I use "COUNT" funtion. If I then send this document to someone who has the French version of Excel does "COUNT" then become "COMPTE" (which is the French equivalent).

Thanks in advance

A:Solved: Converting excel functions from English to French

Read other 6 answers

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?)

A:[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.

Read other 2 answers

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

A:Solved: Excel VBA Enter Date

Read other 13 answers

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



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

Read other 8 answers

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,

A:Solved: EXCEL Date formats

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

Read other 3 answers


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.

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

Read other 2 answers

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.

A:Solved: Excel Enter Date

Read other 13 answers

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 !


A:Solved: Excel Date range

Read other 16 answers

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]

A:(Solved) Excel Date format


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


Read other 3 answers

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?




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

Read other 3 answers

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.


A:Solved: Excel Sort by Date

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

Read other 2 answers

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

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?


A:Solved: Excel 2003, Calculating a date

Read other 7 answers

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!

A: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

Read other 2 answers

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

A: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

Read other 1 answers

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.

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

Read other 9 answers

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

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

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

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

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

Thanks a bunch.

A:Solved: Date format - Excel 2007

Hi Traci.

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

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

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

Read other 3 answers

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.

A:Solved: Show last edit date in Excel ?

Read other 7 answers