£0.00 @

Hi there,
Thought I would get some advice on Excel.
I am looking at some data and have 2 cells, one had the time/date it started and the other time/date it finished.
Is there any formul… Read More

Hi there,

Thought I would get some advice on Excel.

I am looking at some data and have 2 cells, one had the time/date it started and the other time/date it finished.

Is there any formula which I can use to find out the difference between the 2 cells? so I can make some comparison etc?

Thanks!

Thought I would get some advice on Excel.

I am looking at some data and have 2 cells, one had the time/date it started and the other time/date it finished.

Is there any formula which I can use to find out the difference between the 2 cells? so I can make some comparison etc?

Thanks!

Options

## All Comments

(28) Jump to unreadPost a commentNot that it overly matters you can still simply do a sum minus finish time/date from start time/date and then format it in time/date format.

So for instance A column is Date start, B is date finish

=Sum(B1-A1) ta da! which should give the result of x days

Edited By: Nailez on Jun 22, 2011 10:17Not that it overly matters you can still simply

do a sum minus finish time/date from start time/date and then format it in time/date format.Yep that would do it!

Both time and date will be needed.....how we would type that formula?

also the date and time for start are just in one cell..

Edited By: SkippyDoo on Jun 22, 2011 10:22Select the cell then choose Format Cells > Custom > then type: dd/mm/yyyy - hh:mm

Edited By: Mellow Yellow on Jun 22, 2011 10:23Select it then choose Format Cells > Custom > type dd/mm/yyyy - hh:mm

Aye but to get a true record of the differences that would not work for date formats as it would try to format the date from the difference of DAYS not actual date so from 01/01/10 difference to 01/01/11 it would return 30/12/1900 and not 365.

You are correct in what you are trying to do yes but in this instance with dual dates and differences I dont believe this would be the answer.

Edited By: Nailez on Jun 22, 2011 10:25Select it then choose Format Cells > Custom > type dd/mm/yyyy - hh:mm

Aye but to get a true record of the differences that would not work for date formats as it would try to format the date from the difference of DAYS not actual date so from 01/01/10 difference to 01/01/11 it would return 30/12/1900 and not 365.

OK then assuming the data is in cell D2 + B2 then try this:

=IF(D2="","",IF(D2-B2(9/24),D2-B2-((1/24)*15),D2-B2-((1/24)*15)+(15/24)),IF((D2-B2)-INT(D2-B2)>(9/24),-(WEEKNUM(D2)-WEEKNUM(B2))*2-(WEEKNUM(D2)-WEEKNUM(B2))*(4/24)+D2-B2-((1/24)*15),-(WEEKNUM(D2)-WEEKNUM(B2))*2-(WEEKNUM(D2)-WEEKNUM(B2))*(4/24)+D2-B2-((1/24)*15)+(15/24)))))

Nailez, you have PM :)

Indeed the amount of reports I've had to do in my time and noone realises how long or indepth the detailing and behind the scenes work is. They just through them or delete them *sigh*

Formula is =IF(C2="","",IF(C2-B2(9/24),C2-B2-((1/24)*15),C2-B2-((1/24)*15)+(15/24)),IF((C2-B2)-INT(C2-B2)>(9/24),-(WEEKNUM(C2)-WEEKNUM(B2))*2-(WEEKNUM(C2)-WEEKNUM(B2))*(4/24)+C2-B2-((1/24)*15),-(WEEKNUM(C2)-WEEKNUM(B2))*2-(WEEKNUM(C2)-WEEKNUM(B2))*(4/24)+C2-B2-((1/24)*15)+(15/24)))))

Format = d "days" hh "hours" mm "mins"

Edited By: Mellow Yellow on Jun 22, 2011 10:45Simpler version of the same thing but both would work, with the OPEN columns format them as pure numbers without looking at your worksheet its hard what your trying to get to but image might help.

Start & Finish being your time frames in the format of 01/01/10 12:00:00.

Hmm looking at your data now this provides slightly messy results with the use of ended concatenates two secs.

Edited By: Nailez on Jun 22, 2011 11:00: Non relavant see later posts.Formula = =IF(C2="","",IF(C2-B2(9/24),C2-B2-((1/24)*15),C2-B2-((1/24)*15)+(15/24)),IF((C2-B2)-INT(C2-B2)>(9/24),-(WEEKNUM(C2)-WEEKNUM(B2))*2-(WEEKNUM(C2)-WEEKNUM(B2))*(4/24)+C2-B2-((1/24)*15),-(WEEKNUM(C2)-WEEKNUM(B2))*2-(WEEKNUM(C2)-WEEKNUM(B2))*(4/24)+C2-B2-((1/24)*15)+(15/24)))))

Format = d hh:mm

Again too long winded with arguements im afraid.

You simply need to Subtract A from B (or B from A) and then format the result as DD:HH:MM:SS

There giving you a start date, end date and result in the format you require.

As Neil says, if you simply need a rounded amount of days, just use =Days360(A,B)

26/04/2009 12:16 26/04/2010 12:16

in the case of those two dates that would give 360 days not 365 days, this is due to DAYS360 being exactly that 360 days are presumed per year by excel and not 365 1/4.

For reference date is one of the hardest to work out completely accurately due to leap years its a well known issue with excel and caused me a few headaches in the past also which meant I had to lookup a leap year field if it matched within those years then remove 1+ day from the calculation to align the days correctly.

Edited By: Nailez on Jun 22, 2011 11:07: more info.and no its not a hard thing to do stub but each method has its own drawbacks.

dd/mm/yyyy hh:mmas format? on Start and Finish columns and on the other columns they should be formatted as number.Just noticed what you might be saying there A and B are NOT shown as correct dates but those are the dates you originally gave me in PM its just a excel view to show formula's so ignore the data in A and B only use the formula's information as written in C D E F.

Edited By: Nailez on Jun 22, 2011 11:13: clarityA1,B1,"d") to get an exact number of days (Not accounting(for future reference))Certainly alot cleaner in alot of ways to sum's and could replace my calculation in column C, also you could replace d with m to get months

Edited By: Nailez on Jun 22, 2011 11:36: ..Now for trend seeking...if you have any tips on using excel for this...please feel free to let me know

Thanks!