28 day expiry formula calendar

  flobby 23:25 04 Jan 2017
Locked

Hi, I am doing a calender for an autoclave and I need expiry dates inserting into the next cell that is 28 days later in the format 28-Jan-17 e.g. 1st January next cell below I need 29 Jan-17, 2nd January next cell below 30-Jan-17 and so on. Thank you Flobby

  hastelloy 07:40 05 Jan 2017

Format all cells to be Date in the format you want. If your original cell is A1 then in A2 enter =A1+28. Copy this across the row.

  flobby 09:08 05 Jan 2017

Hi Hastelloy. Unfortunately I only have a number in square A1 as in 1.2.&3, but in A2 I require 28-Jan-17 Thank you Flobby

  Belatucadrus 12:16 05 Jan 2017

I only have a number in square A1

Is this the first date in the sequence that you have entered manually. If so then you need to format the data in the cell to Date as hastelloy said, after that the function =A1+28 will automatically increment the second cell to the date 28 days later.

  flobby 23:48 05 Jan 2017

I have the calander set out as a normal one please see [IMG][click here]1 you flobby

Thank

  hastelloy 08:16 06 Jan 2017

That's a very odd set up. You only need 1 cell for each date - you seem to be using 6.

  flobby 08:43 06 Jan 2017

I Know, I am in the process of doing that now, that is why I am asking, I know it probably isn't possible, having the 2 differant date formats, but I thought it was worth a try,The old one was made with merged cells . Cheers Flobby

  Belatucadrus 11:19 06 Jan 2017

The only way around it that I can think of is to change the format of the manual number date in the large white cell to a date format DD this will continue to display just a date as you have at the moment but would require all the dates to be reentered in DD/MM/YY format for the formula to work in the grey background cells.

  lotvic 22:35 06 Jan 2017

I had a go at this and here's my offering:

I set the Row Height to 50 on Rows 2, 4, 6, 8, 10, 12 and 14.

I shaded grey the odd rows, then unshaded Column A

Column B formatted to Custom Date ddmmmyy. I did this so that I could check the dates were correct. (The even rows can be selected at end and formatted to Custom Date d)

Did not need to format rest of columns C to BB as they get copied across

I only needed to make entries in columns A (Weekdays), column B (as per pic), and cell C2

In B2 I typed 30/12/2016 and the rest of Formulas as per pic (to show formatting hold down Ctrl and press ` (top left of keyboard) repeat to unshow formatting)

The rest were able to be copied across by:

  1. selecting the cell in C2 and then dragging the + at bottom right across the row to whatever column you want. (You haven't said whether each set is for a month or wants to go right across to column BB to 29 Dec 2017)

  2. selecting B3 and then dragging the + at bottom right across the row to whatever column you want.

  flobby 23:08 06 Jan 2017

Thank you Lotvic, that looks exacly what I am looking for. I will set it up next week on my days off. Thank you once again, Cheers, Flobby

  lotvic 00:29 07 Jan 2017

Happy it helped :) Be aware you can't type any extra text in any of the cells. If you want to add text via excel you will have to add an extra row to each weekday.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy Book 2: Release date, price and specs

This First Man soundtrack vinyl cover art is exquisitely composed

How to watch Apple’s October 2018 iPad launch

Meilleurs VPN (2018)