Date and Time Formula

edq · Mar 19th, 2002, 05:55 AM

G'Day Excel Gurus...

I'm trying to set up an excel spreadsheet which allows me to calculate the number of minutes between 2 Dates and times.

i.e minutes between 1/03/2002 15:00 and 2/03/2002 15:30

Any Help would be appreciated !

Anne Troy · Mar 19th, 2002, 06:25 AM

You may want to do a search before asking questions; this is a fairly common one.

If A1 contains 1/3/2002 15:00
If B1 contains 2/3/2002 15:30

Format C1 as number with 2 decimals.
Put =(B1-A1)*60

edq · Mar 19th, 2002, 07:02 AM

Thanks for your help...

Believe me I've searched EVERYWHERE for an answer to this.

Tried your formula and formated the cell as you mentioned, but it does not do the calculation. Am I missing something ?

Russell · Mar 19th, 2002, 07:33 AM

I think you should multiply by 24*60 to get minutes. The difference is calculated in fractions of a day.

Russell

Dave Hawley · Mar 19th, 2002, 07:41 AM

Hi edq

If you time and date cells are A1 and A2 you could use:

=MAX(A1:B1)-MIN(A1:B1)

and Custom format the cell as [m]

Or to actually get the real number (as apposed to format) try:

=TEXT(MAX(A1:B1)-MIN(A1:B1),"[m]")*1

egeslani · Mar 19th, 2002, 07:51 AM

Dreamboat's formula is right, you have to multiply by 24 for the difference in day and I wish you will get the answer right.

I use C1=(B1-A1)*60*24

HTH
ernie

edq · Mar 19th, 2002, 03:18 PM

Thanks to All for your help on this one...

I've got it working the way I want now

Mar 19th, 2002, 06:25 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	You may want to do a search before asking questions; this is a fairly common one. If A1 contains 1/3/2002 15:00 If B1 contains 2/3/2002 15:30 Format C1 as number with 2 decimals. Put =(B1-A1)*60 __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 05:55 AM	#1
edq New Member Join Date: Mar 2002 Posts: 3	G'Day Excel Gurus... I'm trying to set up an excel spreadsheet which allows me to calculate the number of minutes between 2 Dates and times. i.e minutes between 1/03/2002 15:00 and 2/03/2002 15:30 Any Help would be appreciated !

Mar 19th, 2002, 07:02 AM	#3
edq New Member Join Date: Mar 2002 Posts: 3	Thanks for your help... Believe me I've searched EVERYWHERE for an answer to this. Tried your formula and formated the cell as you mentioned, but it does not do the calculation. Am I missing something ?

Mar 19th, 2002, 07:33 AM	#4
Russell New Member Join Date: Mar 2002 Posts: 39	I think you should multiply by 24*60 to get minutes. The difference is calculated in fractions of a day. Russell

Mar 19th, 2002, 07:41 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi edq If you time and date cells are A1 and A2 you could use: =MAX(A1:B1)-MIN(A1:B1) and Custom format the cell as [m] Or to actually get the real number (as apposed to format) try: =TEXT(MAX(A1:B1)-MIN(A1:B1),"[m]")*1

Mar 19th, 2002, 07:51 AM	#6
egeslani New Member Join Date: Feb 2002 Location: Jeddah,Saudi Arabia Posts: 27	Dreamboat's formula is right, you have to multiply by 24 for the difference in day and I wish you will get the answer right. I use C1=(B1-A1)6024 HTH ernie

Mar 19th, 2002, 03:18 PM	#7
edq New Member Join Date: Mar 2002 Posts: 3	Thanks to All for your help on this one... I've got it working the way I want now