Dealing with date, time and subtraction

K

kilosub

Board Regular
Joined
Jan 7, 2009
Messages
116
Hi All,

How do I compute 10/7/2015 0:02 41023 - 10/6/2015 0:02 40773?

The time I've format it to h:mm. Below is the part of the data which is recorded hourly



abc
10/7/20150:0241,023.00
10/6/201523:0241,012.00
10/6/201522:0241,002.00
10/6/201521:0240,991.00
10/6/201520:0240,980.00
10/6/201519:0240,969.00
10/6/201518:0240,958.00
10/6/201517:0240,947.00
10/6/201516:0240,936.00
10/6/201515:0240,925.00
10/6/201514:0240,914.00
10/6/201513:0240,904.00
10/6/201512:0240,893.00
10/6/201511:0240,882.00
10/6/201510:0240,871.00
10/6/20159:0240,860.00
10/6/20158:0240,849.00
10/6/20157:0240,838.00
10/6/20156:0240,827.00
10/6/20155:0240,816.00
10/6/20154:0240,806.00
10/6/20153:0240,795.00
10/6/20152:0240,784.00
10/6/20151:0240,783.00
10/6/20150:0240,773.00

<tbody>
</tbody>


Thank you and regards,


Norman
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
10/07/201500:0241,023.0029.0411.00
10/06/201523:0241,012.00
assumes dates are july 10 and june 10
29.04 days
.04 days = 24 * .04 hours0.96 hourswhich is57.6 minutes.6 minutes is 36 secs36
29.040.9657.636
take integers
290573611your 3rd column

<colgroup><col><col><col><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>
 
Upvote 0
Thanks for looking into my problem, oldbrewer..

The date format is format cell: date, dd/mm/yyyy as it was import via txt file


I need to compute 41023 minus 41012 with a check to ensure it's 24 hours. meaning if starts 0ct 6 at 0:02 and end on oct 7 at 0:02.



Thanks in advance
 
Upvote 0
I'm putting the link to worksheet which will have better picture of it.

https://app.box.com/s/w5zx30hi1ivwzpopawhc77xzrul28by0



below is the formula that use
=SUM(IF(AND((B3+1)=RAW!A493,TIME(0,2,)=RAW!B493),RAW!C493,0)-(IF(AND(B3=RAW!A517,TIME(0,2,)=RAW!B517),RAW!C517,0)))

then i found I have major flaw to it:-
1. the raw data by itself start with latest date whenever i obtain the latest data. This data goes on for months... :(
2. when the data is huge i need to correct the row number (it will be time consuming)as for the column (A), (B) and (C) is fix


Thanks in advance..
 
Upvote 0
then i found I have major flaw to it:-
1. the raw data by itself start with latest date whenever i obtain the latest data. This data goes on for months... :(
2. when the data is huge i need to correct the row number (it will be time consuming)as for the column (A), (B) and (C) is fix

so you need to insert new data at the top of the spreadsheet - or - add it at the bottom and sort by column A descending

what do you mean correct the row number ?

I need to compute 41023 minus 41012 with a check to ensure it's 24 hours.

what is 41023 and the above difference is 11
 
Upvote 0
Hi

As far as I can tell, you want to subtract the value in C for the corresponding date at the same time a month prior. If this is the case, the following formula will work for you, pasting your data into cell A1:

Code: 
=C2-SUMIFS(C$2:C$26,A$2:A$26,DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),B$2:B$26,B2)

More generally:

Code: 
=Value-SUMIFS(ValueRange,DateRange,DATE(YEAR(RequiredDate),MONTH(RequiredDate)-1,DAY(RequiredDate)),TimeRange,RequiredTime)

Hope that helps

Mackers
 
Upvote 0
Yes the data keep inserting at the top of the spreadsheet

oops!! correction it should be 41023 minus 40773. My mistake, my eyes getting blur to fill up the cell and upload the link here..


Thank you, sir!
 
Upvote 0
Hi Mackers,


Thanks for dropping in. I don't understand the second code that you provided. How do I use that?


Thank again...
 
Upvote 0
oops!! correction it should be 41023 minus 40773. that is 250 how does that relate to 24 hours
 
Upvote 0
I taking as begin 6th Oct 2015 at 0:02, reading 40773 and cut off on 7th Oct 2015 at 0:02, reading 41023. I wanted a 24 hours to have it cut off. I may be wrong. What's will you suggest?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,665
Messages
6,120,801
Members
448,992
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top