I have a list of dates and times for example 01/08/13 05:27, 03/08/13 07:54
I want to round the times only up so they would always record as 9am ie. 01/08/13 09:00 and 03/08/13 09:00

Any suggestions guru's ?
TIA
Col

If you only need the date variable, just use the date and ignore the time?

I need the date ANd time to "start the clock" and then calculate (always from 9.00am) the duration in days and hours to the next event.
For example the clock starts at 03/08/13 9:00 regardless of arriving sooner , but the finish could be 03/08/13 10:54 or 05/08/13 15:50 and i need to calculate from the start time being 9:00!

Start date in one column, End date in another

=(LEFT(B6,50))-(LEFT(A6,5)+0.375)


Start date column A, end date column b in this case, format the result of the formula to display days, hours whatever


David

Close, I'm impressed
(I changed 9.00 to 7.00)
But it's not handling days
As pic its OK if it's same day, but if it goes over to the next day or more its ignoring that!

dates



[Edited on 23/8/13 by ReMan]

Hello,
You need to change the format from hh:mm:ss you have, to dd:hh or dd:hh:mm

Cheers
David

change format in the time taken column

David

Execl records time/ date as a number, e.g. 36542.05 translates to a time/ date in dd/mm/yy hh:mm format. the whole numbers relate to the date, the numbers past the decimal point relate to the time, ie 0.5 is halfway between two days so equals midday. if I understand correctly your calculations always have a 9am start time on any given date but can end at any time/ date?

if that's correct then you only need to record the date for your start time/ date, then work out the decimal value that relates to 9am and add it to your date on each line. e.g. 9am = 0.37... for start time/ date you record the date, e.g. 1/1/09 is recorded by excel as 37502, so the start value is 37502.37. assuming an end date of 9pm the next day, by my example that'd be 37502.87. do the sums and set the format right excel will display it in the usual time/ date format for you...

Sorry, late night , but I'm home now, having another look at this.
Still struggling.

Using line 2 example Formatting as HMS gives 4:11:40
Formatting as DHHMM gives 00/01/1900

I also have 16000 lines with a months worth of different start dates and times.
I want them to start on the specific event date, but force a start time of say 8:00

JPS cant seem to get your appoach working either, perhaps its something to do wit my formatting?

Ideally the results would be eg for line 2 3h and 9 mins and for line 3 2days 3h 42 mins, or 2.3 days

To complicate further I'l like to remove saturday and sunday too, but ned to get this bit right first!

If the time aspect works already then you can extract the numbers of days by using:

=SUM(DATE(YEAR(B2),MONTH(B2),DAY(B2)))-(DATE(YEAR(A2),MONTH(A2),DAY(A2)))

quote:

---

Originally posted by ReMan
Sorry, late night , but I'm home now, having another look at this.
Still struggling.

Using line 2 example Formatting as HMS gives 4:11:40
Formatting as DHHMM gives 00/01/1900

I also have 16000 lines with a months worth of different start dates and times.
I want them to start on the specific event date, but force a start time of say 8:00

JPS cant seem to get your appoach working either, perhaps its something to do wit my formatting?

Ideally the results would be eg for line 2 3h and 9 mins and for line 3 2days 3h 42 mins, or 2.3 days

To complicate further I'l like to remove saturday and sunday too, but ned to get this bit right first!

---

Thanks David.
I'm glad it's easy for you, it's not for me!

If you u2u me your email I'll send some sample data, I'm trying and understand and learn it though

Sorry Reman,
As I get older I get even more grumpy

I have sent my e mail details

Cheers
David

Me too, but not with you just my lack of skill in this modern stuff
I've emailed you now thanks :-)