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

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

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.

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

Format-Cells, Time.

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

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.

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

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

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?

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.

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.

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.

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

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.

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.

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

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

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

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

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.

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

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!

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

Ted, what software are you talking about?

Are there always 8 values in the date part?

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.

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

The Roundup function should be what you are looking for?

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 ?

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

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

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

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?

never mind I got it. Thanx anyways.

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!

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

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.

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!

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

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.

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

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

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?

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

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?

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

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

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

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.

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!

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

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]

Hiya

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

eddie

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

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

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

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 .

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

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.

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

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.

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

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.

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.

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.

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.

Hello:

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)

DIRECTIONS:

Employee start date: The date (B3) is valid if it is:

o on, or after, the “year begin” date in F3, and

o on, or before, the “year end” date in F4, and

o on, or before, the employee end date in B4

• Employee end date:

The date (B4) is valid if it is:

o on, or after, the “year begin” date in F3, and

o on, or before, the ̶... Read more

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

=TODAY()=A2

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

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

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

Code:

Range("A2").Value = Date

or

Code:

ActiveCell.Value = Date

Regards,

Rollin

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?

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

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

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