Can anyone help me with an excel formula to calculate time without having to put colon in between hours and minutes?
I have a formula that works with the colon but I need one without, in a 24 hour format.
example:
1400 & 1515 = 75
NOT 14:00 & 15:15.
I'm trying to make an excel file for use in flight as a pilot and I just want something simple to just punch in as 24 hour, 4 digit format without mucking around to bring up the colon separator.
A1 = 1400
B1 = 1515
Try:
=(TEXT(B1,"0\:00")-TEXT(A1,"0\:00"))*1440
Worked Amazing! Many thanks and this forum has helped yet again
Last edited by AliGW; 01-01-2018 at 02:10 AM. Reason: Unnecessary quotation removed.
Here is my tweak on this excellent solution, just in case the time entered goes past midnight:
=(TEXT(IF(B1<A1,B1+2400,B1),"0\:00")-TEXT(A1,"0\:00"))*1440
Data Range
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
It's not the answer to your question, but I use Autocorrect to help me with entering times. I have '++' set to autocorrect to ':' so that I can enter 14++35 and get the time 14:35 without leaving the number pad on my computer.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks