Calling all Excel wizards! Help - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# Calling all Excel wizards! Help

5y, 5m agoPosted 5 years, 5 months ago
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!
5y, 5m agoPosted 5 years, 5 months ago
Options

(28)
#1
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 on Jun 22, 2011 10:17
#2
Nailez
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!
#3

Both time and date will be needed.....how we would type that formula?
#4
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 on Jun 22, 2011 10:22
#5
can post screenie of your excel book layed out and ill try to assist
#6
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

Edited By: Mellow Yellow on Jun 22, 2011 10:23
#7
Mellow Yellow
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 on Jun 22, 2011 10:25
banned 1 Like #8
I hate excel. I've come to the conclusion that people only ever input data into a spreadsheet, nobody ever actually reads them!
#9
Nailez
Mellow Yellow
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.

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)))))
#10
Didn't let me enter that, said there are too many arguments

Nailez, you have PM :)
#11
nothing coming through im afraid.
1 Like #12
master_chief
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*
#13
Exactly! lol....a lot of effort goes into them!
#14
Sent PM :)
#15
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"

Edited By: Mellow Yellow on Jun 22, 2011 10:45
#16

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 on Jun 22, 2011 11:00: Non relavant see later posts.
#17
Mellow Yellow
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.
#18
If you just require the number of days between the 2 dates you could use the formula =DAYS360(A1,B1)
#19
Beat me to it Stubbarama
#20
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)
#21
NeilGY
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.

#22

and no its not a hard thing to do stub but each method has its own drawbacks.
#23
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?
#24
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 on Jun 22, 2011 11:13: clarity
1 Like #25
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))
#26
Stubbarama
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 on Jun 22, 2011 11:36: ..
1 Like #27
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!
#28
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