Change day of a date after every 24 lines

[emortals]

Hi All,

In column A i want to fill it with date (dd-mm-yyyy) but I only want the days to tick over to the next day after every 24 cells down as that's when it hits the next day 12:00 AM. Reason is in column B I have a bunch of data that is sorted by hours starting from 12:00 AM through to 11:00 PM and then loops. I want to use column A to associate a date for every block of time from 12:00 AM through to 11:00 PM. Spreadsheet attached.

Not sure where to begin but I would imagine I would have to manually enter a date column A in order for the formula to know which date to start from.

I would be grateful for a solution to this.

Sample file here: date-to-hours.xlsx

Thanks!

[MarvinP]

Hi,

Here is the easy way. Select the cells from A2 to A25. A2 will have the date and all the others will be blank. Double click the right lower corner of the selected range and it will auto fill what you want.

[emortals]

Hi Marvin,

Thank you for the reply. That did help but I also need to fill the gaps with the dates. For example from A2 to A25 they should all be 1/12/2014 and then from A26 to A49 it should be 2/12/2014 and so on.

Also, I just noticed the hours in column B are text "12:00 AM GMT+11:00" - how can I convert this to an actual time "12:00 AM" ? I don't mind if the solution requires me to insert another column to convert it.

thanks again and I await your response.

[alansidman]

Try this:

Please Login or Register  to view this content.

This should do what you want providing you list 24 hours of events. Your current worksheet does not do this.

[FlameRetired]

You also have a "gap" in the timeline. At row 109 the time jumps from 10 am to 12 pm.

[emortals]

you're right, in fact there seems to be a few gaps. Okay the data doesn't seem to be consistent I might have to leave out the date.

Can I get help on converting just the time in text to a real time ?

The text time is formatted like this "12:00 AM GMT+11:00" but i just need "12:00 AM"

Thanks Guys!

[emortals]

Thanks! but it seems there was some inconsistency in the data and some hours were missing. I might have to leave out the date.

Can I get help on converting just the time in text to a real time ?

The text time is formatted like this "12:00 AM GMT+11:00" but i just need "12:00 AM"

Thanks Guys!

[emortals]

Try this:

Please Login or Register  to view this content.

This should do what you want providing you list 24 hours of events. Your current worksheet does not do this.

Thanks! but it seems there was some inconsistency in the data and some hours were missing. I might have to leave out the date.

Can I get help on converting just the time in text to a real time ?

The text time is formatted like this "12:00 AM GMT+11:00" but i just need "12:00 AM"

Thanks Guys!

[MarvinP]

Hi,

Put this formula in A3 and copy it down.

=IF(AND(MID(B2,7,2)="PM",MID(B3,7,2)="AM"),A2+1,A2)

I'd then copy the whole column A and Paste it over itself using Values Only.

[FlameRetired]

Try

Please Login or Register  to view this content.

The double unary "--" coerces the text "time" into its underlying numeric value. Then format for time.

[samba_ravi]

A3=if(left(b3,8)="12:00 am",a2+1,a2)
try this and copy towards down

[emortals]

FlameRetired's solution seemed to work best - thanks all!

[alansidman]

Try this:

Please Login or Register  to view this content.

[emortals]

Hi,

Put this formula in A3 and copy it down.

=IF(AND(MID(B2,7,2)="PM",MID(B3,7,2)="AM"),A2+1,A2)

I'd then copy the whole column A and Paste it over itself using Values Only.

this worked up until line 25 - it couldn't interpret 11:00 PM GMT+11:00

[emortals]

Try

Please Login or Register  to view this content.

The double unary "--" coerces the text "time" into its underlying numeric value. Then format for time.

this worked perfectly for the text time conversion

[emortals]

Try this:

Please Login or Register  to view this content.

Wow, that was impressive. I am still reviewing this one but it seems to be a kick a$$ solution - all at a click of a button.

[emortals]

Try this:

Please Login or Register  to view this content.

WOW, this was an impressive solution and worked perfectly. It auto replaced the text based times with real times at a click of a button. Very nice!

[samba_ravi]

A3=if(left(b3,8)+0<left(b2,8)+0,a2+1,a2)
try this and copy towards down

i hope it will solve your problem

[FlameRetired]

Glad these helped, and thanks for the rep!