I’m working with a datetime number like 44396.33333 in Google Sheets and need to get just the time part. When I try using the TIMEVALUE function, it gives me an error saying it can’t parse the number to date/time.
Here’s what I tried:
=TIMEVALUE(44396.33333)
But it returns: TIMEVALUE parameter ‘44396.33333’ cannot be parsed to date/time.
I know I could use TIME(HOUR(cell), MINUTE(cell), 0) but my actual formula is pretty long already and I’m looking for a more direct approach. Is there a cleaner way to strip out just the time decimal portion from this type of datetime serial number?
yeah, timevalue only handles text, not raw numbers. just subtract the integer part - try =44396.33333-INT(44396.33333) to get .33333, then format that cell to time. it works great for datetime serials.
TIMEVALUE expects text, not a numeric serial number. Since your datetime is already in Excel’s serial format, just use MOD to grab the fractional part: =MOD(44396.33333,1). You’ll get 0.33333 - that’s your time portion. Want it formatted as actual time? Wrap it with TIME formatting or use =TEXT(MOD(44396.33333,1),"h:mm AM/PM"). I use this all the time with imported timestamp data that comes as serial numbers instead of formatted datetime strings.
The fractional part of serial datetime numbers is the time component. Extract it with the FRAC function: =FRAC(44396.33333). This grabs the decimal portion directly - no MOD or subtraction needed. If you don’t have FRAC, use =44396.33333-TRUNC(44396.33333) instead. Same result, just uses TRUNC rather than INT. Once you’ve got the decimal portion, format the cell as time so it displays correctly. This works great for large datasets since it’s just one function call.