Calling all Excel wizards! Help

Found 22nd Jun 2011
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!

Groups
1. Misc

time AND date or just time?

Not 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" 22nd Jun 2011

Nailez

time AND date or just time?Not that it overly matters you can still … time AND date or just time?Not 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!

Original Poster

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

Original Poster

as the formula I try just gives me another date! lol

also the date and time for start are just in one cell..
Edited by: "SkippyDoo" 22nd Jun 2011

can post screenie of your excel book layed out and ill try to assist

You need to change the format of the cell with the answer.
Select the cell then choose Format Cells > Custom > then type: dd/mm/yyyy - hh:mm

Mellow Yellow

You need to change the format of the cell with the answer.Select it then … You need to change the format of the cell with the answer.Select 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" 22nd Jun 2011

Banned

I hate excel. I've come to the conclusion that people only ever input data into a spreadsheet, nobody ever actually reads them!

Nailez

Aye but to get a true record of the differences that would not work for … 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)))))

Original Poster

Didn't let me enter that, said there are too many arguments

Nailez, you have PM

nothing coming through im afraid.

master_chief

I hate excel. I've come to the conclusion that people only ever input … I hate excel. I've come to the conclusion that people only ever input data into a spreadsheet, nobody ever actually reads them!

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*

Original Poster

Exactly! lol....a lot of effort goes into them!

Original Poster

Sent PM

Try this instead, data in cell B2 + C2.
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"

Simpler 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" 22nd Jun 2011

Mellow Yellow

Try this instead, data in cell B2 + C2.Formula = … Try this instead, data in cell B2 + C2.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.

If you just require the number of days between the 2 dates you could use the formula =DAYS360(A1,B1)

Beat me to it Stubbarama

I cant see the problem ?

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)

NeilGY

If you just require the number of days between the 2 dates you could use … If you just require the number of days between the 2 dates you could use the formula =DAYS360(A1,B1)

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" 22nd Jun 2011

and no its not a hard thing to do stub but each method has its own drawbacks.

Original Poster

Just done that, however now converting those numbers to date and time mode are giving me a random date when I convert it to date format?

using dd/mm/yyyy hh:mm as 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" 22nd Jun 2011

It wont help on this but if you can use =datedif(A,B,"d") to get an exact number of days (Not accounting(for future reference))

Stubbarama

It wont help on this but if you can use =datedif(A1,B1,"d") to get an … It wont help on this but if you can use =datedif(A1,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" 22nd Jun 2011

Original Poster

Thanks so much guys! Have got there! You are a definitely helpful bunch!

Now for trend seeking...if you have any tips on using excel for this...please feel free to let me know

Thanks!

Glad could help with trends that is a little too specific for my tastes as would need to know alot more information than 2 dates gl