CODEWITHSUNDEEP
excelexcel-formula
Sergey
Sergey

Reputation: 682

Specific date and time range in Excel

I have twelve events every day from 8am to 8pm every hour.

I have two columns in Excel spreadsheet: "date and time" and "description".

I need to add date and time stams for each cell of "date and time" column in the format 

16/11/2011 08:00

So the first twelve cells will look like that:

16/11/2011 08:00
16/11/2011 09:00
16/11/2011 10:00
16/11/2011 11:00
16/11/2011 12:00
16/11/2011 13:00
16/11/2011 14:00
16/11/2011 15:00
16/11/2011 16:00
16/11/2011 17:00
16/11/2011 18:00
16/11/2011 19:00
16/11/2011 20:00

Then the next day shall come:

17/11/2011 08:00
17/11/2011 09:00
17/11/2011 10:00
17/11/2011 11:00
17/11/2011 12:00
17/11/2011 13:00
17/11/2011 14:00
17/11/2011 15:00
17/11/2011 16:00
17/11/2011 17:00
17/11/2011 18:00
17/11/2011 19:00
17/11/2011 20:00

Is there a way I can get the column filled in automatically for several months?

Upvotes: 1

Views: 6930

Answers (1)

JMax
JMax

Reputation: 26611

Put your first date in cell A1: 

16/11/2011 08:00

And in cells A2 and below, add the formula:

=A1+TIME(IF(HOUR(A1)=20,12,1),0,0)

and then drag down your formula till where you need.

[EDIT] Explaining a little bit the formula: in order to keep the formula quite simple, it is rather specific.
Meaningly,

  • the formula starts at the previous date A1 +
  • then, it adds some hours (with the TIME function) but no minutes nor seconds (0,0) i.e. see the end of the formula
  • to choose the number of hours to add, it checks wether the previous date hour is 8pm (i.e. HOUR(A1) = 20).
    • If so, it adds 12 hours so that the new date is at 8am the next morning.
    • If not, it adds only 1 hour (next event)

[EDIT 2] New formula to skip week-ends (it checks wether the previous date is a friday (WEEKDAY=6) and hour is 8pm (HOUR(A1)=20). If so, it adds two days (just add 2 because date in Excel are stored as serial number and unit is a day).

=A1+TIME(IF(HOUR(A1)=20,12,1),0,0) + IF(AND(HOUR(A1)=20,WEEKDAY(A1)=6),2,0)

Upvotes: 3

Related Questions