Welcome Guest
You last visited December 7, 2016, 3:49 pm
All times shown are
Eastern Time (GMT-5:00)

How do you get a "Day" from a "Date"

Topic closed. 11 replies. Last post 8 years ago by KnuckleHead.

 Page 1 of 1

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: April 29, 2009, 2:59 pm - IP Logged

Hi all,

If your working on creating a "histories" file and you want to figure out what day of the week a "Date" is, try this:

@IF(@CELL("type",B80..B80)="b","??",@IF(@WEEKDAY(B80)=0,"Mon",@IF(@WEEKDAY(B80)=1,"Tue",@IF(@WEEKDAY(B80)=2,"Wed",@IF(@WEEKDAY(B80)=3,"Thu",@IF(@WEEKDAY(B80)=4,"Fri",@IF(@WEEKDAY(B80)=5,"Sat",@IF(@WEEKDAY(B80)=6,"Sun","N/A"))))))))

The "B80" refers to the cell that has a date (ie: 4/2/2009) in it. This formula can be set into the next cell so that as you enter your historical dates, it will tell you what day of the week it is without having to dig out a calander. You will have to replace all of the "B80" references with whatever cell address your "Date" goes into.

The "??" refers to the "date" cell that is blank.

The "N/A" tells you that no "Day" matches the weekday. (If you look at the formula, all 7 days of the week are included in the formula.)

For those of you that use Excel, I believe you have to replace the "@" with "=" in every instance.

Hope this can help someone...

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: April 29, 2009, 3:08 pm - IP Logged

Why not just use the built-in format for this?

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: April 29, 2009, 4:06 pm - IP Logged

Why not just use the built-in format for this?

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: April 29, 2009, 5:00 pm - IP Logged

(highlight all the cells you want to use) --> Format --> Cells --> Number --> Custom --> (in the type field, put) --> ddd (or) dddd --> [ENTER]

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 5, 2009, 10:04 pm - IP Logged

(highlight all the cells you want to use) --> Format --> Cells --> Number --> Custom --> (in the type field, put) --> ddd (or) dddd --> [ENTER]

Thanks time*treat,

I tried your suggestion, but it doesn't work in my version of Lotus123, so I needed something like I described.

My thought was that someone else migt have run into the same problem and this would be a "work around" to help in building their "histories".

Thank you for the suggestion though, I didn't know that you could do that in Excel.

Zeta Reticuli Star System
United States
Member #30470
January 17, 2006
10351 Posts
Offline
 Posted: May 5, 2009, 11:39 pm - IP Logged

Not Excel (well maybe the site is using Excel) but here's another way:

http://www.timeanddate.com/calendar/

Those who run the lotteries love it when players look for consistency in something that's designed not to have any.

There is one and only one 'proven' system, and that is to book the action. No matter the game, let the players pick their own losers.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 6, 2009, 8:20 am - IP Logged

Not Excel (well maybe the site is using Excel) but here's another way:

http://www.timeanddate.com/calendar/

Thank you Coin Toss,

I didn't know that the calendar was there. That could be very useful. There's quite a lot on the web if you think about what to look for. I just didn't think to look for it.

Ppreaciate it.

New Jersey
United States
Member #17843
June 28, 2005
49758 Posts
Offline
 Posted: May 6, 2009, 10:46 am - IP Logged

Hi all,

If your working on creating a "histories" file and you want to figure out what day of the week a "Date" is, try this:

@IF(@CELL("type",B80..B80)="b","??",@IF(@WEEKDAY(B80)=0,"Mon",@IF(@WEEKDAY(B80)=1,"Tue",@IF(@WEEKDAY(B80)=2,"Wed",@IF(@WEEKDAY(B80)=3,"Thu",@IF(@WEEKDAY(B80)=4,"Fri",@IF(@WEEKDAY(B80)=5,"Sat",@IF(@WEEKDAY(B80)=6,"Sun","N/A"))))))))

The "B80" refers to the cell that has a date (ie: 4/2/2009) in it. This formula can be set into the next cell so that as you enter your historical dates, it will tell you what day of the week it is without having to dig out a calander. You will have to replace all of the "B80" references with whatever cell address your "Date" goes into.

The "??" refers to the "date" cell that is blank.

The "N/A" tells you that no "Day" matches the weekday. (If you look at the formula, all 7 days of the week are included in the formula.)

For those of you that use Excel, I believe you have to replace the "@" with "=" in every instance.

Hope this can help someone...

Cell A2=01/01/2009 or the Date Format you Promote to the (Range Properties) Frequently Used List (2009/01/01)

Cell B2=@DATEINFO(@DATEVALUE(\$A2),1)

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 6, 2009, 10:55 am - IP Logged

Cell A2=01/01/2009 or the Date Format you Promote to the (Range Properties) Frequently Used List (2009/01/01)

Cell B2=@DATEINFO(@DATEVALUE(\$A2),1)

Helo Raven62,

Now I'm gald I posted my formula. It took me quite a while to figure out... That being said, all of you have posted other ways to do the same thing in an easier format. Glad I found this site...I'm more of a computer novice and do not have to experience needed to create what I think of doing.

Thank all of you for the help and suggestions.

New Jersey
United States
Member #17843
June 28, 2005
49758 Posts
Offline
 Posted: May 7, 2009, 7:59 am - IP Logged

Helo Raven62,

Now I'm gald I posted my formula. It took me quite a while to figure out... That being said, all of you have posted other ways to do the same thing in an easier format. Glad I found this site...I'm more of a computer novice and do not have to experience needed to create what I think of doing.

Thank all of you for the help and suggestions.

Glad I was able to help: Not too many folks using "Lotus 123" these days: It'll do pretty much anything you want to do with a Spreadsheet!

Good Luck,

As long as you have a willingness to learn you'll do fine!

A mind once stretched by a new idea never returns to its original dimensions!

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: May 7, 2009, 11:15 am - IP Logged

Thanks time*treat,

I tried your suggestion, but it doesn't work in my version of Lotus123, so I needed something like I described.

My thought was that someone else migt have run into the same problem and this would be a "work around" to help in building their "histories".

Thank you for the suggestion though, I didn't know that you could do that in Excel.

I missed your mention of Lotus, in the OP. I confess to not knowing Lotus1-2-3 was still around.

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 7, 2009, 12:12 pm - IP Logged

I missed your mention of Lotus, in the OP. I confess to not knowing Lotus1-2-3 was still around.

Morning time*treat

No problem, when I first started using a computer, Excel didn't do what I wanted, so since Lotus was pre-loaded, that's all I've ever used. I understand that different things in Excel and Lotus123 can be crossed over and still work. I've even used some examples that I've found for Excel in Lotus. I've just never thought of switching over...so if some of my questions seem odd, no you'll understand why.

Thank you for the intrest.

The only DUMB question is the one question you DID NOT ask...

 Page 1 of 1