Date problem in forumlas

Hi Everyone,

My name is Shane and I am having problem trying to use excel to calculate using dates.
I am using 2010 to start with.

What I am trying to do is set up a spread sheet with a years worth of dates in column a and then modify those dates with 100 in column b.

Now, if I enter the dates as an example 6/29/2010 (american), it doesnt like the /'s. If I format it to a custom mmddyyyy format it still doesnt like it as I think it still recognises the original format as shown in the formula bar.

If I try to just enter it as the correct format (6292010) at the top of column a and try to fill a years worth of dates, it doesnt seem to like it. I cant seem to work out how to make it look at the number as a date so it can fill the rest of the column with out typing every date one at a time.

Any suggestions would be greatfully received. Be gentle as I am a relative newbie to excel.

Shane.

Please post a example of your workbook (without confidential informatie).

Please also give us (in your workbook) the wanted result.

See attached workbook
What I would like is for the dates to run down in column a from the first date for a year or so.
Then I would like to mod that date by 192 after its been turned to a number. 06/29/2010 = 6292010 in number form.

WHAT IS HAPPENING:

DATE DATE MOD 192
06/29/2010 #VALUE!
06/30/2010 #VALUE!
07/01/2010 26/02/1900
07/02/2010 28/03/1900



WHAT I WOULD LIKE:

6292010 170
6302010 186
7012010 170
7022010 186

Look if this is what your up to.

Please report your results.

Do you have questions, just ask.

hi oeldere,
You got the date sorted but the mod figures are wrong.
In long hand:
06292010/192=32770.885416 - 32770 * 192 = 170.
I am not sure why it is doing this.
Any ideas?

I tried to reproduce the formula.

(and the result 170 is the good result of the formula) (off course).

But I think you don't want that answer (170).

Otherwise please give more info, where do you want to use it for.

I am doing some cycle work on stock market cycles. What I am looking to do is subtract out all the "whole 192 cycles" and leave the rest. The rest is what I am out to further interrogate using various formulas. Thing is I can calculate it on the calculator, I have other excel spreadsheets that do this for me on other stocks and it works ok. I have another sheet open and I just rechecked the figures by a calculator and it works correctly. Saying that though, the other sheet isnt modding dates so that may be the problem. example: 7256.34 mod by 667.19 = 584.44

I think that is what the Topic Starter means.

I can't figure out why you wanted this kind of results?

Exactly what I was after.
Thank you to both of you for helping me out.
I didnt realise it was going to end up being such a large formula.Will have to go read up on text formula so I understand how it works.
Martin, btw, I like Mojito made with tequila,lime and sugar syrup instead of rum for something different. Does the trick though lol.

Thanks again,

Shane.

Oeldere,
What I am doing is running a continuos figure through a stocks history. For instance the example I gave above was 7256.34 mod by 667.19 = 584.44 . 7256.34 days has 10 completed cycles and .875 of the 667.19 cycle. I then use that .875 * 667.19 to tell me how long is left in the cycle in days. Certain fractals of the 667.19 mean certain things are likely to happen. Just one example of one stock.
If this means nothing to you then dont worry about it.
Thanks for your help.

Oeldere,
What I am doing is running a continuos figure through a stocks history. For instance the example I gave above was 7256.34 mod by 667.19 = 584.44 . 7256.34 days has 10 completed cycles and .875 of the 667.19 cycle. I then use that .875 * 667.19 to tell me how long is left in the cycle in days. Certain fractals of the 667.19 mean certain things are likely to happen. Just one example of one stock.
If this means nothing to you then dont worry about it.
Thanks for your help.