ReMan
|
| posted on 23/8/13 at 03:14 PM |
|
|
latest Excel challenge
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
www.plusnine.co.uk
|
|
|
|
|
nick205
|
| posted on 23/8/13 at 03:44 PM |
|
|
If you only need the date variable, just use the date and ignore the time?
|
|
|
ReMan
|
| posted on 23/8/13 at 03:51 PM |
|
|
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!
www.plusnine.co.uk
|
|
|
Minicooper
|
| posted on 23/8/13 at 04:16 PM |
|
|
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
|
|
|
ReMan
|
| posted on 23/8/13 at 04:49 PM |
|
|
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]
www.plusnine.co.uk
|
|
|
Minicooper
|
| posted on 23/8/13 at 04:55 PM |
|
|
Hello,
You need to change the format from hh:mm:ss you have, to dd:hh or dd:hh:mm
Cheers
David
|
|
|
Minicooper
|
| posted on 23/8/13 at 04:58 PM |
|
|
change format in the time taken column
David
|
|
|
jps
|
| posted on 23/8/13 at 08:37 PM |
|
|
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...
|
|
|
ReMan
|
| posted on 23/8/13 at 09:24 PM |
|
|
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!
www.plusnine.co.uk
|
|
|
wood85
|
| posted on 23/8/13 at 09:42 PM |
|
|
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)))
|
|
|
Minicooper
|
| posted on 23/8/13 at 11:00 PM |
|
|
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!
This is so easy and straight forward, I have told you how to do it, I will send you my e mail address and I will do it for you, send me a spreadsheet
of what you want
Cheers
David
|
|
|
ReMan
|
| posted on 23/8/13 at 11:27 PM |
|
|
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
www.plusnine.co.uk
|
|
|
Minicooper
|
| posted on 24/8/13 at 08:10 AM |
|
|
Sorry Reman,
As I get older I get even more grumpy
I have sent my e mail details
Cheers
David
|
|
|
ReMan
|
| posted on 24/8/13 at 06:51 PM |
|
|
Me too, but not with you just my lack of skill in this modern stuff
I've emailed you now thanks :-)
www.plusnine.co.uk
|
|
|
