I’m working with a datetime value stored as a number like 44396.33333 in Google Sheets. When I try to use the TIMEVALUE function on this number, I get an error saying the parameter cannot be parsed.
=TIMEVALUE(44396.33333)
This throws: TIMEVALUE parameter '44396.33333' cannot be parsed to date/time.
What I need is to extract just the time portion from this numeric datetime value. I know I could use TIME(HOUR(cell), MINUTE(cell), 0) but I’m looking for a more direct approach since my actual formula is quite complex and I want to keep it concise. Is there a simpler way to strip the date part and keep only the time decimal portion?
just use the MOD function: =MOD(44396.33333,1) and format the cell as time. MOD strips the whole number and keeps the decimal (which is your time). way cleaner than messing with hour/minute functions.
You can also use the FRAC function: =FRAC(44396.33333) - it pulls out the decimal part directly. Works exactly like MOD(value,1) but it’s way clearer what you’re doing, especially in complicated formulas. I found this while working on financial models where I needed other people to understand my formulas for audits. It handles weird edge cases well and there’s no guessing what it does. Format the cell as time and you’ll get the same clean result as the other methods.
The MOD approach works great, but here’s another option that might click better for you. Just subtract the integer portion: =A1-INT(A1) where A1 has your datetime number. You’ll get the same decimal result as MOD, but some people find this more readable since you’re literally stripping out the date part. Both methods give you something like 0.33333 - just format it as time to display correctly. I’ve used both in different spreadsheets and they work identically, so it’s really just personal preference or whatever fits your existing formulas better.