Excel Formula help

[johnnypohlfan]

Sorry for another computer question...


Is there a formula within Excel that will return the day of the week, based on the date?

For example, i have a column of dates, and I'd like the column next to it to show the day of the week that that date corresponds to.

6/1/2010 Tuesday
6/2/2010 Wednesday
etc


I couldn't find anything apparent in Excel, but I thought maybe one of you who's smarter than I would know of one.

Thanks a million!

 

[Kepler]

Re: Excel Formula help

Sorry for another computer question...


Is there a formula within Excel that will return the day of the week, based on the date?

For example, i have a column of dates, and I'd like the column next to it to show the day of the week that that date corresponds to.

6/1/2010 Tuesday
6/2/2010 Wednesday
etc


I couldn't find anything apparent in Excel, but I thought maybe one of you who's smarter than I would know of one.

Thanks a million!

If cell A1 has the date, set cell A2 to:

=TEXT(WEEKDAY(A1), "dddd")

 

[JF_Gophers]

Re: Excel Formula help

If cell A1 has the date, set cell A2 to:

=TEXT(WEEKDAY(A1), "dddd")

It's probably best to insert the date in date format to avoid the Text issues.

The reason is that Weekday uses the date serial number that Excel employs (i.e., Jan 1, 1900 is 1).

Also you can tell Excel if you consider Sunday or Monday as the first day of the week via the WEEKDAY(serial number, return type) function. Return type 1 would return sunday as day 1, 2 would return Monday as day 1.

You can also use CHOOSE() in conjunction with Weekday to get the days names if you aren't using TEXT().

 

[LynahFan]

Re: Excel Formula help

Buzzzzzzz.

The correct answer is: never, ever use Excel for anything, ever. It sucks. The developers think they're sooooo smart that they know what you want it to do better than you do.

"Oh - you used a date format that only displays the date, not the time. Well then, the time must not matter to you. Let me truncate all the date numbers down to integers to save you all that trouble of messing with those pesky floating point numbers, never mind the fact that you'll NEVER EVER be able to retrieve the times of day again. You're welcome! Oh, and don't forget that even though we SAY that the date numbers represent the number of days since Jan 1, 1900, we left out a couple of leap years, so it's two days off. Good luck trying to use this data in any other tool."

 

[JF_Gophers]

Re: Excel Formula help

"Oh - you used a date format that only displays the date, not the time. Well then, the time must not matter to you. Let me truncate all the date numbers down to integers to save you all that trouble of messing with those pesky floating point numbers, never mind the fact that you'll NEVER EVER be able to retrieve the times of day again. You're welcome! Oh, and don't forget that even though we SAY that the date numbers represent the number of days since Jan 1, 1900, we left out a couple of leap years, so it's two days off. Good luck trying to use this data in any other tool."

You're partially right. The serial date is off by 2. But you can retrieve the .xx values even if you use plain date format and then decide to switch to date/time. At least in 2007 you can.

Although I wonder what other program would you want to use the serial date in? wouldn't you just use the actual date?

 

[LynahFan]

Re: Excel Formula help

You're partially right. The serial date is off by 2. But you can retrieve the .xx values even if you use plain date format and then decide to switch to date/time. At least in 2007 you can.

Although I wonder what other program would you want to use the serial date in? wouldn't you just use the actual date?

MATLAB, Mathematica, ROOT, Origin, etc. Or C/C++/FORTRAN to mate up with legacy codes. It's much easier to load in an entire array of numbers rather than having date strings mixed in with the data.

Now that I think about it, the truncation thing may have been when I saved the files as .CSVs so that I could load them into said other programs. I clearly remember it seriously ****ing me off...

 

[Ralph Baer]

Re: Excel Formula help

You're partially right. The serial date is off by 2. But you can retrieve the .xx values even if you use plain date format and then decide to switch to date/time. At least in 2007 you can.

Although I wonder what other program would you want to use the serial date in? wouldn't you just use the actual date?

It has incorrectly 1900 as a leap year. Where is there an error of a second day? It has the next few centuries correct.

 

[Zudnic]

Re: Excel Formula help

=WEEKDAY(A1) where A1 contains a datewill return the day of the week 1-7 where 1 is Sunday.

You can then use
=IF(Weekday(A1)=1,"Sun",IF(Weekday(A1)=2,"Mon",IF(Weekday(A1)=3,"Tues",IF(Weekday(A1)=4,"Wed",IF(Weekday(A1)=5,"Thu",IF(Weekday(A1)=6,"Fri",IF(B2=7,"Sat")))))))

My dad used to call stuff like this UBE - ugly but effective.

 

[JF_Gophers]

Re: Excel Formula help

=WEEKDAY(A1) where A1 contains a datewill return the day of the week 1-7 where 1 is Sunday.

You can then use
=IF(Weekday(A1)=1,"Sun",IF(Weekday(A1)=2,"Mon",IF(Weekday(A1)=3,"Tues",IF(Weekday(A1)=4,"Wed",IF(Weekday(A1)=5,"Thu",IF(Weekday(A1)=6,"Fri",IF(B2=7,"Sat")))))))

My dad used to call stuff like this UBE - ugly but effective.

That is ugly considering there has already been two better choices posted.

=TEXT(WEEKDAY(A1), "dddd")

and

=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

 

[LynahFan]

Re: Excel Formula help

It has incorrectly 1900 as a leap year. Where is there an error of a second day? It has the next few centuries correct.

I don't know - I never isolated it. But I do remember the offset being 2, not 1.

Regardless, it's ludicrous that Microsoft got this wrong, and continues to insist on using its own screwy date system.

 

[Ralph Baer]

Re: Excel Formula help

I don't know - I never isolated it. But I do remember the offset being 2, not 1.

Regardless, it's ludicrous that Microsoft got this wrong, and continues to insist on using its own screwy date system.

I made an Excel spreadsheet that calculates the date number for January 1 of each starting in 1900. Then I subtracting each year's Jan 1 value from the next and there does not seem to be another error.

I just googled this and the reason that Excel has this wriog is that they wanted to agree with Lotus 1-2-3 and Lotus had it wrong. At least that is what they say.

 

[JF_Gophers]

Re: Excel Formula help

I don't know - I never isolated it. But I do remember the offset being 2, not 1.

Regardless, it's ludicrous that Microsoft got this wrong, and continues to insist on using its own screwy date system.

Ralph, 40176 serially should be Jan 1, 2010. Instead its 12/29/2009.

There has been 26 leap years since 1900 for 9516 days. 84 non leap years for 30660 days.

1900 and 2000 were not leap years due to being divisible by 4 and 100.

 

[Patman]

Re: Excel Formula help

=WEEKDAY(A1) where A1 contains a datewill return the day of the week 1-7 where 1 is Sunday.

You can then use
=IF(Weekday(A1)=1,"Sun",IF(Weekday(A1)=2,"Mon",IF(Weekday(A1)=3,"Tues",IF(Weekday(A1)=4,"Wed",IF(Weekday(A1)=5,"Thu",IF(Weekday(A1)=6,"Fri",IF(B2=7,"Sat")))))))

My dad used to call stuff like this UBE - ugly but effective.

I've had the habit of constructing a lookup table... its been how I've usually handled sports score data since I lacked the knowledge and ability to handle team names as text strings.

I'd set up the lookup table outside of the relevant region (or printing region) and make a call to that.

Personally I'd rather do it that way then deal with a nesting of "IF" statements

 

[Ralph Baer]

Re: Excel Formula help

Ralph, 40176 serially should be Jan 1, 2010. Instead its 12/29/2009.

There has been 26 leap years since 1900 for 9516 days. 84 non leap years for 30660 days.

1900 and 2000 were not leap years due to being divisible by 4 and 100.

2000 was a leap year because it is divisible by 400. I can even remember back that far.

 

[LynahFan]

Re: Excel Formula help

Ralph, 40176 serially should be Jan 1, 2010. Instead its 12/29/2009.

There has been 26 leap years since 1900 for 9516 days. 84 non leap years for 30660 days.

1900 and 2000 were not leap years due to being divisible by 4 and 100.

But 2000 was, because it's divisible by 400.

I agree with you that Excel is off by 2, but I don't know where/why.

 

[JF_Gophers]

Re: Excel Formula help

2000 was a leap year because it is divisible by 400.

Oh then that really confuses me as to why they would be off 2 instead of the 1.

 

[johnnypohlfan]

Re: Excel Formula help

Thanks to those who helped. I think I have what I need to complete my report.

 

[Ralph Baer]

Re: Excel Formula help

The error of including 2/29/1900 means that I have credited Yale, which held the Belt from 1/28/1899 to 2/7/1901, with an extra day of possession of the Belt.

 

[LynahFan]

Re: Excel Formula help

The error of including 2/29/1900 means that I have credited Yale, which held the Belt from 1/28/1899 to 2/7/1901, with an extra day of possesion of the Belt.

Gadzooks!

Alert Microsoft to this travesty of justice and I'm sure that will bring them around to deciding to correct their algorithm.

 

[dxmnkd316]

Re: Excel Formula help

Sorry for another computer question...


Is there a formula within Excel that will return the day of the week, based on the date?

For example, i have a column of dates, and I'd like the column next to it to show the day of the week that that date corresponds to.

6/1/2010 Tuesday
6/2/2010 Wednesday
etc


I couldn't find anything apparent in Excel, but I thought maybe one of you who's smarter than I would know of one.

Thanks a million!

The easiest way is just to format the cell using a custom number format "DDDD"

No equation necessary.