Welcome Guest
Log In | Register )
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
PrintE-mailLink
KnuckleHead's avatar - box

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

    time*treat's avatar - radar

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

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

      KnuckleHead's avatar - box

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

      Because I hadn't found it...

        time*treat's avatar - radar

        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.

          KnuckleHead's avatar - box

          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.

            Coin Toss's avatar - shape barbed.jpg
            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.

            Lep

            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.

              KnuckleHead's avatar - box

              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.

                Raven62's avatar - binary
                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!

                  KnuckleHead's avatar - box

                  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.

                    Raven62's avatar - binary
                    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.

                    Hi KnuckleHead,

                    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!

                      time*treat's avatar - radar

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

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

                        KnuckleHead's avatar - box

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

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