TImes

[xxsalahxx]

Hello I have a spreadsheet formatted to time format. With over 1000 times.
Im trying to calculate the times between 2 cells, whether its a plus or minus. So for example if cell a2 is 19:00 and cell b2 is 19:30 there is difference of -30 minutes as its late.
However if cell b2 is 16:30 then its a plus 02:30 hours as its early..

Any ideas how i can work this out?
Thanks in advance for any help anyone can give me

 

[ranman256]

you need date as well as time to do elapsed time in case it crossed midnight.
1/1/2022 19:30, 1/2/2022 4:30,

 

[Fluff]

Is this what you want?

+Fluff 1.xlsm
	A	B	C
1
2	19:00	19:30	-30
3	19:00	16:30	150
Primary

Cell Formulas
Range	Formula
C2:C3	C2	=(A2-B2)*1440

If not please post some sample data giving expected results.

 

[xxsalahxx]

Hello Yes this is partly what I require, is there a way I can do this?

1st dose given	4 Hours Between	2nd Dose due	2nd Dose given (actual time)
03:00​	04:00​	07:00​	07:30​	00:30​		30 mins late
09:30​	04:00​	13:30​	13:00​	########​		want it to show 30 mins early
21:00​	04:00​	01:00​	02:00​	########​		Want this to show 1 hour late

 

[Fluff]

But that is what the formula does

+Fluff 1.xlsm
	A	B	C	D	E
1	1st dose given	4 Hours Between	2nd Dose due	2nd Dose given (actual time)
2	03:00	04:00	07:00	07:30	-30
3	09:30	04:00	13:30	13:00	30
4	21:00	04:00	01:00	02:00	-60
Primary

Cell Formulas
Range	Formula
E2:E4	E2	=(C2-D2)*1440

 

[xxsalahxx]

you need date as well as time to do elapsed time in case it crossed midnight.
1/1/2022 19:30, 1/2/2022 4:30,

Thank you.

But that is what the formula does

Thanks for all your help, When I put this range in mine is showing differently, will this be a formatting issue do you think? third row of data should be -60 but its showing 1380!

1st dose given	4 Hours Between	2nd Dose due	2nd Dose given (actual time)
03:00​	04:00​	07:00​	07:30​	-30​	30 mins late
09:30​	04:00​	13:30​	13:00​	30​	want it to show 30 mins early
21:00​	04:00​	01:00​	02:00​	1380​	Want this to show 1 hour late

 

[Fluff]

Check that none of those times contain a date element.

 

[xxsalahxx]

Check that none of those times contain a date element.

All seem to be time elements no dates at all.

 

[Fluff]

Then I don't understand how you get 1380 as that is 23hrs.
Did you format the cells to show dates?

 

[xxsalahxx]

Then I don't understand how you get 1380 as that is 23hrs.
Did you format the cells to show dates?

Got it now!!! thank you for all your help. phew!