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

Hi,

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

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

Here is my code:

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

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

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

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

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

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

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

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

It gives me a valid time in the cell.

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

Is there a work around to this?

I'm working on Excel 2003 on Windows XP SP2

Thank you
Yanick

RELEVANCY SCORE 200
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
RELEVANCY SCORE 88.4

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)

=SUM((A1-TRUNC(A1))*0.6)+TRUNC(A1)

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

=SUM(TRUNC(A1)+((A1-TRUNC(A1))/0.6))

--

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

Thanks
 

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
RELEVANCY SCORE 82.4

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

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

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

Any help would be greatly appreciated!

Found the answer I was looking for:

Assuming the first cell is A1,

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

Copy down, then copy the column and paste values.

You can also use

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

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

Read other answers
RELEVANCY SCORE 78.4

Hi,

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,

Robin
 

A:Solved: Excel VBA Overflow Error

Read other 13 answers
RELEVANCY SCORE 74

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

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

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

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

AND

CLIENT , Non Billable - at column level

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

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

Hi there, welcome to the board!

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

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

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

Read other 2 answers
RELEVANCY SCORE 70.8

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?

Thanks.
 

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
RELEVANCY SCORE 70.8

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
RELEVANCY SCORE 70.8

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
RELEVANCY SCORE 70.8

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
RELEVANCY SCORE 70.8

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
RELEVANCY SCORE 70

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
RELEVANCY SCORE 70

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

0.##

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

13
13.25

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

13.
13.25

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.

HTH
 

Read other 2 answers
RELEVANCY SCORE 68

Hi Guys,

I have this code:
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")

ws2.Range("A2:IV300").ClearContents
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
Next
MsgBox ("Update Next Sheet!")
ws2.Select
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:

Code:
Dim I As Long, lstrow As Long
Jimmy
 

Read other 2 answers
RELEVANCY SCORE 65.6

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

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

I have attached the excel file I want to use.

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

Any help would be greatly appreciated, thanks!
 

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

Read other 16 answers
RELEVANCY SCORE 63.6

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

Thanks
Ted
 

A:Converting complex date text fields into date or numbers

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

Read other 3 answers
RELEVANCY SCORE 63.6

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

A:Solved: Converting custom date format into text

Read other 8 answers
RELEVANCY SCORE 61.6

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

=E18-INT(E18)
=MOD(E18,1)
=E18-TRUNC(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
RELEVANCY SCORE 61.6

Hi,

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

=ROUNDUP(range,0)
 

Read other 2 answers
RELEVANCY SCORE 61.6

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
RELEVANCY SCORE 61.6

Hi,

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
RELEVANCY SCORE 61.6

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

A:a decimal Number in Excel

The Roundup function should be what you are looking for?
 

Read other 2 answers
RELEVANCY SCORE 61.2

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
RELEVANCY SCORE 61.2

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
RELEVANCY SCORE 60.4

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

Thanks!
 

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

Pedro
 

Read other 1 answers
RELEVANCY SCORE 58.8

howd'y

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
RELEVANCY SCORE 58.8

Hello

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

Greta
 

A:Solved: Converting .wb3 files to Excel

Welcome to the board.

Try this?
 

Read other 2 answers
RELEVANCY SCORE 58.4

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
RELEVANCY SCORE 58

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
RELEVANCY SCORE 57.6

Hello,

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
RELEVANCY SCORE 57.2

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
RELEVANCY SCORE 56.4

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 !

Moll
 

A:Solved: Excel Date range

Read other 16 answers
RELEVANCY SCORE 56.4

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

Column A
09/20/2012
09/21/2012
10/20/2012
02/20/2012

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

10/20/2012
09/20/2012
09/21/2012
02/20/2012

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.

Help!
 

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
RELEVANCY SCORE 56.4

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,
Andy
 

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
RELEVANCY SCORE 56.4

Hello,

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:

Date
27-Oct-08
28-Oct-08
29-Oct-08
30-Oct-08
31-Oct-08
3-Nov-08
4-Nov-08
5-Nov-08
6-Nov-08
7-Nov-08
10-Nov-08
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...
Code:
=IF(WEEKDAY(A2)=6,A2+2,IF(WEEKDAY(A2)=7,A2+1,A2))+1
HTH
 

Read other 2 answers
RELEVANCY SCORE 56.4

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:

YEAR(NOW())-YEAR(F6)

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?

Regards,

TBaker14

 

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
RELEVANCY SCORE 56.4

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

Thanks,

Peaker.
 

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

Read other 8 answers
RELEVANCY SCORE 56.4

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?

Thanks
[email protected]
 

A:(Solved) Excel Date format

Hiya

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

eddie
 

Read other 3 answers
RELEVANCY SCORE 56.4

I have the following code
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]
Else
ws.Range("K4") = Date + (7 - Weekday(Date, 1))
End If
 

A:Solved: Excel VBA Enter Date

Read other 13 answers
RELEVANCY SCORE 56.4

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
RELEVANCY SCORE 56.4

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

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

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

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

A:Excel 2010: Cell Date Change with Current Date

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

Read other 1 answers
RELEVANCY SCORE 56

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

Sub TestUserInput()
'
' Macro1 Macro
'
'
Dim MyDate As Date
Columns("O:O").Select
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"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
 

A:Solved: Excel 2007 Date Variable

Read other 7 answers
RELEVANCY SCORE 56

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

Suggestions?

Thanks,
gpence
 

A:Solved: Excel formula -- by date summary

Read other 7 answers
RELEVANCY SCORE 56

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:
12/26/2000
and cell 2 contains:
31-Dec-11

what is the formula to get the result?
 

A:Solved: excel - date difference in years

Read other 6 answers
RELEVANCY SCORE 56

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?

Thanks
 

A: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,""))
 

Read other 1 answers
RELEVANCY SCORE 56

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?
 

A:Solved: Excel 2007 date stamp

Read other 7 answers
RELEVANCY SCORE 56

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?

Thanks
 

A:Solved: Excel 2003, Calculating a date

Read other 7 answers