#Excel formula to add days to date serial#
#Excel formula to add days to date code#

I9 contained the Date the delivery would be dispatched. This is simplified by using a return_type of 2 so that weekdays are 5 and below: To do this I used an IF statement to check if the simple Date + X would be a weekend and if so add the required number of days on. Recently I needed to calculate when deliveries would arrive avoiding any deliveries over the weekend. Numbers 1 (Thursday) through 7 (Wednesday). Numbers 1 (Wednesday) through 7 (Tuesday). Behaves like previous versions of Microsoft Excel.

If you're using a version of excel before 2007 the optional return_type doesn't exist and Excel behaves as if the value is '1'.If you don't include the return_type (as I didn't above) then Excel assumes 1.

#Excel formula to add days to date code#

The return_type entry changes the number code for each day in the week (see the table below).

However if you have typed it into the calculation then it will not work, in which case you'll need to use the DATE function within the calculation:=WEEKDAY(DATE(2017,02,20))

#Excel formula to add days to date serial#

If you have the date in a cell (A1) this is fine because excel converts it into a serial as soon as you type it into the cell.

the date needs not to be in a '20-02-2017' type format in the calculation.

The syntax for this is =WEEKDAY(Date as serial number, return_type). Use WEEKDAY to return a number (normally 1-7) corresponding to the day of the week. ='Period is ' & if(DATEDIF(A1, A2, 'y') = 0, '',DATEDIF(A1, A2, 'y') & ' years and ') & DATEDIF(A1, A2, 'm') & ' months' Calculating the start of a weekī1-(WEEKDAY(B1)-2) Find the day of the week This can be avoided with an IF statement to check what value the 1 st DATEDIF will return. Obviously if the A1 and A2 are in the same year this would return a '0 years' element which might not be desirable. ='Period is ' & DATEDIF(A1, A2, 'y') & ' years and ' & DATEDIF(A1, A2, 'm') & ' months' This function can be very useful if you want to return a text string for instance 'Period is X years and Y months'. Note that if you have the interval in another cell you can use DATEIF(A1, A2, B1) however B1 needs to contain m with no quotes around it. The syntax of the DATEDIF calculation is =DATEDIF(date1, date2, interval)Īssuming the dates are in A1 and A2, to find the whole months between A1 and A2Ĭomplete calendar months between the dates.Ĭomplete calendar years between the dates.Ĭomplete calendar months between the dates as if they were of the same year.Ĭomplete calendar days between the dates as if they were of the same year.Ĭomplete calendar days between the dates as if they were of the same month and same year. Using the MONTH calculation means that you also need to check that the YEARS are the same otherwise you could under calculate the difference if the period covers one or more year ends. It has been there since very early versions but tends not to be documented. If you want to know how many whole months or years lie between two dates you can either use the Month() calculation or a rather well hidden DATEDIF function which is a hangover from the migration from Lotus 123. Calculating how many months or years lie between two dates Number of working days between two dates. This calculation assumes the 'start' is at the beginning of the day and the 'end' is at the end of the day so is A1 = A2 the formula will return 1 working day. If you have the Start in A1, the end date in A2 and some holidays in the range B1:b5. Working days between two datesĮxcel has a great formula NETWORKDAYS which calculates the week days between a start date and end date and also removes defined holidays. You can also just add a number into the calculation in place of '+a2'. Obviously if you want to add years you can move the +a2 up into the year section of the calculation. If you have your date in A1 and the number of months to be added in A2: To add whole moths (or years) to a date use the DATE formula which builds a date up from the year, month and day. You can't just add days to a date to make the 1 st of January become the 1 st of April. = EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0))+1,7) Adding months to a date Note that this also allows you to find the date of the end of the month in X months time by replacing 0 with X Finding the last Friday of the monthĪgain with a date in A1 the following calculation returns the last Friday in the month. If you have a date in A1 then the end of the month is

Working with dates in Excel is fairly easy however here are some tricks that I have found useful: Finding the end of a month