Convert cell format from custom h:mm to minutes

[ChristinaStitt]

data in a cell is formatted in h:mm which is truly a result of a calculation of # of hours & minutes detained at a location. Data is result of microstrategy query so result is
e.g. 17:08
cell is formatted as custom h:mm, but there is actually a fictitious date of 1/1/1900 defaulting in front of h:mm when double clicking into cell or viewing in fx field above. How do I get rid of that date which is inhibiting me from converting 17:08 to minutes by using the formula of =TEXT(L3,"[m]")

[daddylonglegs]

What result do you get? Try

=TEXT(MOD(L3,1),"[m]")

[Pete_UK]

That date/time really indicates 1 day and 17 hours and 8 minutes, so you just need to use this formula instead:

=L3*24*60

which should give the answer of 2468 minutes. Format that cell as General or as Number.

Hope this helps.

Pete

[redsab]

I have played around with the format custom h:mm. I believe you are using the wrong format. This is for time only. Time can either be displayed in 12 hour time with AM/PM or it can display in military time 00:00 - 23:59. If you enter in a time that is outside the normal range, that is when you get the fictitious date.

Eg)

8:00 = 8:00AM
20:00 = 8:00PM

If you enter something outside that range, that is when you get the date issue

Eg)

34:00 That is not a legal time, therefore, it gives you that strange date and time. To you it may look like 2,040 minutes, but the computer is looking for a legal time to display.

24:65 To you this may look like 1,505 minutes, but the computer again can not give you a correct time because you have went outside the time parameters.

What I would do is use two seperate columns to display your numbers, then you could calculate the minutes with out any problem. I have attached a small worksheet for you to look at so you can see how you could reformat your worksheet.