VideoPhoto

Showing posts with label weekday function. Show all posts
Showing posts with label weekday function. Show all posts

03 December, 2021

WeekDay and BirthDay

You know your birthday date, no problem. Do you know on which day of the week you were born? If not, you can find it out quite easily in Excel. There are many ways to do it, but probably the simplest one is to use the WEEKDAY function, as shown in this snip:

04 September, 2021

How to DATE in Excel

This is about dating with the Excel DATE... function, and with its versatile formats and forms of uses. It's syntax is simple but the results of its application can be sometimes a bit confusing or unexpected.

Confusing - because the DATE function with its syntax DATE(year,month,day) doesn't object some strange parameters and accepts numbers such as:

  • negative, 0, and >12 integers for a month,
  • higher number of days than the number of days allowable for a given month, as well as 0 days,
  • negative numbers for a day,
  • number lower than 1900 for a year.

So, e.g. entering accidentally month number 14 would set the date to February of the following year. Entering month number 0 would set the date to December of the previous year. Entering year number 1888 would set the date to the year 3788.

Normally, i.e. in General format, DATE function returns a serial number starting from January 01 in the year 1900, represented by number 1, which was Sunday, and increasing by 1 for every next calendar day. September 05, 2021 is the 44444  special day in this sequence, and it's Sunday as well.

If you need to deal with dates before the year 1900 you can access relevant information here:

MrExcel

28 April, 2021

Date to date

Date functions in Excel can answer a whole plethora of questions we are dealing with in everyday life. Some of them are quite straightforward, and some not quite, but all have practical applications. 

Couple of notes, before you try to use any of the formulas:

  • MONDAY is designated here as the first day of the week
  • Cells with the formulas shown here must be in General format, not Date format.
  • Cells A2:C2 are presented in Custom format: "dddd dd/mm/yyyy"

Useful tip: you can enter Current date using this shortcut: CTRL+; (semicolon)

Formulas presented here, when supported with a simple setup in a worksheet, like this one:

will help you to find many of your questions answered. Here are some examples:

How many days left in this calendar year:    =DATE(YEAR(A2),12,31)-A2

Number of specific week days between two dates, the second date inclusive. Use the following formulas for the number of:

  • Mondays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=1)*1)
  • Tuesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=2)*1)
  • Wednesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=3)*1)
  • Thursdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=4)*1)
  • Fridays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5)*1) 
  • Saturdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=6)*1) 
  • Sundays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=7)*1) 

Number of weekdays (Monday-Friday) between two dates, the second date inclusive:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,2,3,4,5})*1)

Number of weekend days (Saturday-Sunday) between two dates, the second date inclusive: 

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={6,7})*1)

Number of business days, with holidays excluded:

=NETWORKDAYS.INTL(A2,B2,1,Freedays), where 'Freedays' represents a range of holiday dates set up and named so in your worksheet (if needed).

Number of days survived since somebody's birthday (entered in cell C2):

 =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&A2)),2)={1,2,3,4,5,6,7})*1)-1

Number of days left till the end of this century (including 31 Dec, 2100):

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(TODAY()&":"&DATE(2100,12,31))),2)={1,2,3,4,5,6,7})*1)