advancing dates

[Andrew Z Carpenter [Newsgroup Groupie]]

Hi folks,

Here's one for you wizards out there! In cell A1, I enter
a 'Starting Date', for example 22/09/2003

In a column I have times, for example the following:

21:00
21:30
22:00
23:00
00:00
01:00
01:30
02:00
02:30

What I need is for it to enter that date in the column
next to the time, but when the time reaches midnight,
for the date to advance to the next day, like so:

21:00 22/09/2003
21:30 22/09/2003
22:00 22/09/2003
23:00 22/09/2003
00:00 23/09/2003
01:00 23/09/2003
01:30 23/09/2003
02:00 23/09/2003
02:30 23/09/2003


00:00 might not always be on the list, for example it
could go straight from 23:00 to 02:15.

If I were to change the 'Starting Date', the dates in
the column should also change to reflect this.

I'm sorry I'm asking such basic questions, but like I
said earlier, excel isn't my forté!

Thanks very much,

Andrew

 

[Andy B]

Andrew

In the second column you just need to advance the date one day if the time
in the first column is less than the previous entry
Try this:
=IF(H12<H11,I11+1,I11)

Andy.

 

[Andy Brown]

00:00 might not always be on the list, for example it
The only thing I would add to Andy B, & only because it was not covered in
the original question ... *if* it could go straight from eg: 00:30 you'd
need to throw in an IF to cover, or plug in the first date manually.

Rgds,
Andy

 

[Andrew Z Carpenter [Newsgroup Groupie]]

message
Andrew

In the second column you just need to advance the date one day if the time
in the first column is less than the previous entry
Try this:
=IF(H12<H11,I11+1,I11)

Andy.

Andy,

Thanks for the reply. I don't have the spreadsheet with me here at work,
but when I get home this evening I will try it out! Thanks very much,

Andrew