Excel Tip: Evaluate part of a formula directly in the formula bar
Quite an unassuming tip today, but potentially useful (and not immediately obvious).
In Excel, if you have a complex formula within a cell, you can immediately evaulate part of it by selecting the part (dragging across it with the mouse or by using arrow keys), then press F9.
For example, if you have:
=cos(3+2/5)
then you select the 3+2/5 part:
=cos(3+2/5)
and press F9:
=cos(3.4)
This is useful for simplifying formulae, and also diagnosis when something isn't working as expected. Note that if you press enter (or click on another cell), it will save the formula with the evaluated number in the cell, rather than the original one. To avoid saving it, press Escape when you're done editing.
So for the above example, enter saves the formula =cos(3.4) in the cell, while pressing escape returns it to =cos(3+2/5).
Categories: VBA / Excel Tags:
How To: Get the Weekday as Text in Excel VBA
I often want to know what day of the week a date is. Excel has a function for this, but unfortunately it only returns an integer, for example the function:
=WEEKDAY("01/02/2009")
returns 1 (which represents Sunday).
This can be a bit confusing with a list of dates, so I wrote the following quick VBA function to get the day of the week as text, e.g. Sunday, Monday, etc.
The function (possibly not the most elegant method possible) is:
' function to return a text representation of the weekday of a given date
' returns the full text, which can be shortened by left(), etc
Function dayText(d As Date) As String
If Weekday(d) = 1 Then dayText = "Sunday"
If Weekday(d) = 2 Then dayText = "Monday"
If Weekday(d) = 3 Then dayText = "Tuesday"
If Weekday(d) = 4 Then dayText = "Wednesday"
If Weekday(d) = 5 Then dayText = "Thursday"
If Weekday(d) = 6 Then dayText = "Friday"
If Weekday(d) = 7 Then dayText = "Saturday"
End Function(Enter this via the VBA Editor - press Alt+F11, then insert -> module and paste this code in).
It can then be called by:
=DAYTEXT("01/02/2009")
which returns Sunday.
This can then be shortened to one or three letters via the left() function, for example:
=LEFT(DAYTEXT("01/02/2009"),3)
returns Sun.
If you want to avoid macros, an alternative method is to use the (slightly messy) formula consisting of nested IFs, for a spreadsheet with a date in cell A1:
=IF(WEEKDAY(A1)=1, "Sunday", IF(WEEKDAY(A1)=2,"Monday",IF(WEEKDAY(A1)=3, "Tuesday", IF(WEEKDAY(A1)=4, "Wednesday", IF(WEEKDAY(A1)=5, "Thursday", IF(WEEKDAY(A1)=6, "Friday", "Saturday"))))))
Categories: VBA / Excel Tags: date, Excel, VBA, weekday