What time is it right now (e.g. 2:15PM) & how do I calculate off that?

E

Ersh

New Member
Joined
Mar 11, 2009
Messages
4
I'm working on a scheduling tool and I need to run a variety of time calculations. It's hard to explain all the nitty gritty details, but at a high level I'm doing a lot of back and forth conversion between decimals and time. I'm wondering what your thoughts are...do you try to do your math directly on Time fields or is it just as simple to use decimals?

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Label
[/TD]
[TD]Data
[/TD]
[TD]Data Format
[/TD]
[TD]Formula
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (as fraction of 24 hours)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]0.6270
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=NOW()-TODAY()
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (hours)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]15.0474
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=B2*24
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (whole hours)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=LEFT(B3,FIND(".",B3)-1)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (minutes)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]2.8457
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=MID(B3,FIND(".",B3),10)*60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (whole minutes)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=LEFT(B5,FIND(".",B5)-1)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (seconds)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]50.7400
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=MID(B5,FIND(".",B5),10)*60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (whole seconds)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]50
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=LEFT(B7,FIND(".",B7)-1)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]current time (re-constructed)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]3:02:50 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Time
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=TIME(B4,B6,B8)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]end of day (4:45 PM)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]4:45 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Time
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]4:45 PM
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]end of day (4:45 PM as fraction of 24 hours)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]0.6979
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=(16+0.75)/24
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]remaining time (hours)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]1.7026
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=(B11-B2)*24
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]remaining time (minutes)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]42.1543
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=MID(B12,FIND(".",B12),10)*60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]
[TABLE="width: 235"]
<tbody>[TR]
[TD]remaining time (seconds)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 85"]
<tbody>[TR]
[TD]9.2600
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 84"]
<tbody>[TR]
[TD]Number
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 192"]
<tbody>[TR]
[TD]=MID(B13,FIND(".",B13),10)*60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi.

I would always use the built-in date and time functions. Internally they expect the dates to be in whole numbers of days after1/1/1900 and the hours are stored as fractions of days so they make up the fractional part of the number.

If you do that you will have all these functions you can use plus easy use of the built-in date and time formats: https://support.office.com/en-gb/ar...4e4-a2bc-3230a10786fa&ui=en-US&rs=en-GB&ad=GB

The functions will let you add days, subtract dates, calculate working days between two dates, find the end of a month etc etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Rounding Negative time to the nearest minute in Excel
Replies
4
Views
658
Scott Huish
Scott Huish
R
  • Solved
Time in/Out Diff
Replies
5
Views
451
Fluff
Fluff
J
  • Question Question
How To Calculate Hours Minutes Seconds
Replies
8
Views
493
jamiguel77
J
C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
155
Fluff
Fluff
P
  • Solved
Calculate time spent in hourly interval
Replies
2
Views
214
phiero21
P

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top