excel question 2

[pcs800]

I am new with excel, how would i get it to insert the date automatically?
like this, I have these in column A:
Mon
Tues
Wed
Thurs
Fri
Sat
Sun
And I want the date to be automatically inserted into the cell next to the day.


Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[SkipVought]

Hi,

1) In B1
[tt]
=INT((TODAY()-2)/7)*7+2
[/tt]
2) in B2 and following
[tt]
=B1+1
[/tt]
3) In A1 and following
[tt]
=B1
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnnygeo]

I would avoid using the TODAY() function in the first cell, because when you recalculate tomorrow, it will be incorrect. You should put a hard date (you can just press CTRL+; to insert today's date) in one cell, then use Skip's suggestion of adding or subtracting one to get adjacent days.

 

[SkipVought]

Johnny, Johnny, Johnny!

Did you try to analyze my formula???

It "converts" TODAY to a week anchor point. ANY day in the week will work!!!

;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnnygeo]

I think we are interpreting the request differently. Your approach is great if you always want the dates of the *current* week to show up.

I was interpreting the problem as a "historical" document where the dates must remain static. I did mis-speak, however, when I said it will be incorrect "tomorrow". It will be correct (in this context) for up to a whole week.

rgds

 

[SkipVought]

I guess I also ASSUMED that he wanted the CURRENT week because I focuesd on the phrase

insert the date automatically

How else AUTOMATICALLY? ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnnygeo]

In my book, it doesn't get much more automatic than <CTRL+;>, unless you're a cyborg. You aren't are you? hmmm...would explain your remarkable prolificacy...

 

[SkipVought]

Nice shortcut, but NOT automatic, which in my book means...

set it up and let it rip -- it's ALWAYS correct, AUTOMATICALLY!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[pcs800]

ok, will try it. thanks

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[DeLaMartre]

Skip & Johnny,

What a Great Discussion! Stars to both of you.

Thanks for the intellectual stimulation at 5:15 in the morning.

Regards,

-Bob in California

 

[pcs800]

Well alright, post a question - start a major trouble shooting thread.
I have definitely leanred some thing here.
Just for the record, I need it to display the current week, actually the current 2 week period.

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[pcs800]

Ok, i'm doing something wrong. I used the first suggestion and here is what happens.
A B C D
1 mon |38117
|#VALUE!
2 tues |#VALUE!
|#VALUE!
3 wed |#VALUE!
|#VALUE!
4 thurs |#VALUE!

What do i change? in B1 I put your formula of =INT((TODAY()-2)/7)*7+2
and in each day following i put B1+1, B1+2, etc...

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[SkipVought]

1) Format Column B as DATE

2) the formula in B2
[tt]
=B1+1
[/tt]
3) COPY this formula down (NOT =B1+2, =B1+3)

chances are, column B is formatted TEXT



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[pcs800]

ok, i made column B a date format. it now displays todays date, but the rest still say #value!
I just put B1+1 in each cells formula bar.

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[straybullet]

If you put the date in A1 as 5/9/04 (column formatted "custom" as ddd)
And put =A1 in B1 (formatted as "Date") I believe you should get the results you desire.
You can fill down both columns A&B as many days as you like and the result will be:
A B
Sun 5/9/04
Mon 5/10/04
Tue 5/11/04
Wed 5/12/04
Thu 5/13/04
Fri 5/14/04
Sat 5/15/04
Sun 5/16/04


Let them hate - so long as they fear... Lucius Accius

 

[pcs800]

but then i will always have to put a date into A1, i would like it to be automatic if possible. it seems skip is trying to help me accomplish that, but i am not getting it yet.

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[SkipVought]

B1 should NOT display TODAY's date -- should be 5/10/2004

B2 should have
=B1+1

B3 should have
=B2+1

and so on...

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[pcs800]

Ok, i got it working. Will this update itself with the dates each week?
Actually, it is supposed to be a two week pay period I am keeping track of. So will that scew it up?

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com

 

[AnotherHiggins]

Yes, it will update itself each week.

The stated formula,

=INT((TODAY()-[red]2[/red])/7)*7+2

will always start with Monday of the current week.

If you want to start with the Monday of the preceeding week, use

=INT((TODAY()-[red]7[/red])/7)*7+2

Or if you want to start with the Monday before that,

=INT((TODAY()-[red]14[/red])/7)*7+2

The days you take away from TODAY ([red]in red[/red]) determine the starting point. You could use =INT((TODAY()-[red]84[/red])/7)*7+2 to get a rolling 12 week history.

John

 

[pcs800]

ok, thanks

Eric VanLandingham
The Bargain Monkey

http://www.bargainmonkey.com